Replace first occurrence of substring in a string in SQL

Question

I have to fetch data from a @temp table which has something like "or ccc or bbb or aaa" I want to replace the first occurrence into space to get something like this " ccc or bbb or aaa". I am trying stuff and replace but they don't seem to get me the desired result

What I have tried:

DECLARE @stringhere as varchar(500)

DECLARE @stringtofind as varchar(500)

set @stringhere='OR contains or cccc or  '

set @stringtofind='or'
select STUFF('OR contains or cccc or  ',PATINDEX('or', 'OR contains or cccc or  '),0 ,' ')

Reareace

Are you trying to make a where clause for a dynamic query? – Pence

i am trying to get a final value after removing the first occurrence of a string.. – Reareace

Please read this community discussion about urgent begging, and be informed that this is not an appropriate way to address volunteers. Thanks! – Shrift

Answer 1

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'.

Eichelberger

Thank you so much sir, worked absolutely fine... searched hours for this got no good enough result. ... Thank you – Reareace

Glad to help you ... and you asked a pretty good question :-) – Eichelberger

Bit late (but surprised this wasn't noted before), but this only seems to work if there is an occurrence of 'substring'. If there isn't, null is returned. So a CASE expression to catch that is advisable. – Lonni

@Lonni Nice catch. Or, we could just wrap the entire call to STUFF inside COALESCE, and replace null values with the original column. – Eichelberger

This has an issue with collations though: The matching substring may have a different length than the search string ('hä' is in 'haegar' in German_PhoneBook_100_CI_AS_SC_UTF8). – Declarative

Answer 2

it seems you miss 2% preceding and trailing to the target string

please try:

select STUFF(@stringhere, PATINDEX('%' + @stringtofind + '%', @stringhere), LEN(@stringtofind), ' ')

Copt

@user3331798 : please try – Copt

@user3331798 : Thanks, but why there are 2 single quotes in '''OR contains or cccc or '? – Copt

Answer 3

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)

Ascidium

Answer 4

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

Audacity

Answer 5

To modify only the first letters add a "WHEN CHARINDEX(@FindText, @Text) = 1" If not, you risk replacing another letter in your sentence if it is not present.

DECLARE 
    @Text varchar(MAX),
    @TextReplaceBy varchar(20),
    @FindText varchar(20);

SET @Text = 'Text to replace';
SET @FindText = 't';
SET @TextReplaceBy = '';
SELECT
CASE 
    WHEN CHARINDEX(@FindText, @Text) = 1
      THEN STUFF(@Text, CHARINDEX(@FindText, @Text), LEN(@FindText), @TextReplaceBy) 
    ELSE @Text
END

Emmaemmalee