Have you ever thought of How REPLACE is working? It just replaces all the occurrences of a word in the sentence.

Lets look at an example.

CREATE TABLE #StringTable ( id INT, string VARCHAR(128) )

INSERT #StringTable VALUES (1,'It was a pleasant morning, and he was the only kid in the garden')
,(1,'There is no match word')
Declare @searchstring varchar(100)='was',@Replacestring varchar(100)='is'

select id,string,
	Replace(string,@searchstring,@replacestring)
	from #StringTable
		cross apply (select (charindex(@searchstring, string))) as Search1(Pos)

Drop table #StringTable

In the above example, there are two occurrences of the word “was” for the first record. And the REPLACE function results in replacing all occurrences.

Suppose, if you want to only replace the first occurrence, How do you do?

First approach with CROSS APPLY and SUBSTRING:

CREATE TABLE #StringTable ( id INT, string VARCHAR(128) )

INSERT #StringTable VALUES (1,'It was a pleasant morning, and he was the only kid in the garden')
,(1,'There is no match word')

Declare @searchstring varchar(100)='was',@Replacestring varchar(100)='is'

select id,string,
	case when Search1.Pos != 0 then 
				substring(string, 0,Search1.Pos )+ @Replacestring +
				substring(string, Search1.Pos+LEN(@searchstring), LEN(string) )
	else string end ChangedText
	from #StringTable
		cross apply (select (charindex(@searchstring, string))) as Search1(Pos)

Drop table #StringTable

Another method with STUFF and PATINDEX:

CREATE TABLE #StringTable ( id INT, string VARCHAR(128) )

INSERT #StringTable VALUES (1,'It was a pleasant morning, and he was the only kid in the garden')
,(1,'There is no match word')

Declare @searchstring varchar(100)='was',@Replacestring varchar(100)='is'

Select id, string, Case when charindex(@searchstring, string) != 0 Then
				Stuff(string, patindex('%'+@searchstring+'%',string),len(@searchstring),@Replacestring) 
				Else string End ChangedText
	from #StringTable

Drop table #StringTable