在SQL中替换字符串中第一次出现的子字符串

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

我必须从@temp表中获取数据，其中包含类似“或ccc或bbb或aaa”的内容。我想将第一个匹配项替换为空格以获得类似“ccc或bbb或aaa”的内容。我正在尝试和替换，但他们似乎没有得到我想要的结果

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 ,' ')

2 个解决方案

#1

You can use a combination of STUFF and CHARINDEX to achieve what you want:

您可以结合使用STUFF和CHARINDEX来实现您的目标：

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

CHARINDEX（'substring'，col）将返回列中第一次出现'substring'的索引。然后，STUFF用'replacement'替换这种情况。

#2

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

它似乎你错过2％前面和尾随目标字符串

please try:

请尝试：

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

#1