替换 SQL 中字符串中第一次出现的子字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38911588/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Replace first occurrence of substring in a string in SQL
提问by Shobhit92
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 ,' ')
回答by Tim Biegeleisen
You can use a combination of STUFFand CHARINDEXto 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 firstoccurrence of 'substring'in the column. STUFFthen replaces this occurrence with 'replacement'.
CHARINDEX('substring', col)将返回列中第一次出现的索引'substring'。 STUFF然后用'replacement'.
回答by Grace
it seems you miss 2%preceding and trailing to the target string
似乎您错过%了目标字符串之前和之后的2 个
please try:
请尝试：
select STUFF(@stringhere, PATINDEX('%' + @stringtofind + '%', @stringhere), LEN(@stringtofind), ' ')
回答by RPh_Coder
You can do a CHARINDEXor a PATINDEX, as shown above, but I would also recommend adding a COALESCE, in case your @stringtoFindit not included in your @stringhere.
您可以执行 aCHARINDEX或 a PATINDEX，如上所示，但我也建议添加 a COALESCE，以防您@stringtoFind的@stringhere.
SELECT COALESCE(STUFF(@stringhere, PATINDEX('%' + @stringtofind + '%', @stringhere), LEN(@stringtofind), ' '), @stringhere)
