Replace first occurrence of substring in a string in SQL
You can do a CHARINDEX
or a PATINDEX
, as shown above, but I would also recommend adding a COALESCE
, in case your @stringtoFind
it not included in your @stringhere
.
SELECT COALESCE(STUFF(@stringhere, PATINDEX('%' + @stringtofind + '%', @stringhere), LEN(@stringtofind), ' '), @stringhere)
I had the same problem and made the same response as Tim Biegeleisen, but in a function:
CREATE FUNCTION DBO.FN_REPLACE_FIRST(@X NVARCHAR(MAX), @F NVARCHAR(MAX), @R NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS BEGIN
RETURN STUFF(@X, CHARINDEX(@F, @X), LEN(@F), @R)
END
So I just call the function instead:
SELECT DBO.FN_REPLACE_FIRST('Text example', 'ex', 'eexx') --> Returns 'Teexxt example'
The explanation is the same
it seems you miss 2%
preceding and trailing to the target string
please try:
select STUFF(@stringhere, PATINDEX('%' + @stringtofind + '%', @stringhere), LEN(@stringtofind), ' ')
You can use a combination of STUFF
and CHARINDEX
to achieve what you want:
SELECT STUFF(col, CHARINDEX('substring', col), LEN('substring'), 'replacement')
FROM #temp
CHARINDEX('substring', col)
will return the index of the first occurrence of 'substring'
in the column. STUFF
then replaces this occurrence with 'replacement'
.