29

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 ,' ')
halfer
  • 19,824
  • 17
  • 99
  • 186
CrazySwazy
  • 403
  • 1
  • 5
  • 11
  • Are you trying to make a where clause for a dynamic query? – Deep Aug 12 '16 at 07:04
  • i am trying to get a final value after removing the first occurrence of a string.. – CrazySwazy Aug 12 '16 at 07:21
  • Please [read this community discussion](http://meta.stackoverflow.com/q/326569/472495) about urgent begging, and be informed that this is not an appropriate way to address volunteers. Thanks! – halfer Aug 12 '16 at 08:25

4 Answers4

52

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

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thank you so much sir, worked absolutely fine... searched hours for this got no good enough result. ... Thank you – CrazySwazy Aug 12 '16 at 07:07
  • Glad to help you ... and you asked a pretty good question :-) – Tim Biegeleisen Aug 12 '16 at 07:07
  • 6
    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. – HoneyBadger Jan 25 '18 at 10:42
  • 2
    @HoneyBadger Nice catch. Or, we could just wrap the entire call to `STUFF` inside `COALESCE`, and replace null values with the original column. – Tim Biegeleisen Jan 25 '18 at 10:48
  • 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). – John Sep 08 '22 at 16:42
3

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

please try:

select STUFF(@stringhere, PATINDEX('%' + @stringtofind + '%', @stringhere), LEN(@stringtofind), ' ')
Grace
  • 876
  • 1
  • 11
  • 21
2

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)
RPh_Coder
  • 833
  • 8
  • 15
2

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

Erick de Vathaire
  • 166
  • 2
  • 5