替换 SQL 中字符串中第一次出现的子字符串 [英] Replace first occurrence of substring in a string in SQL

问题描述

我必须从@temp 表中获取数据，该表具有或 ccc 或 bbb 或 aaa"之类的内容，我想将第一次出现的内容替换到空间中以获得类似ccc 或 bbb 或 aaa"的内容.我正在尝试一些东西并替换，但它们似乎没有让我得到想要的结果

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

我尝试过的:

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

推荐答案

您可以结合使用 STUFF 和 CHARINDEX 来实现您想要的:

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) 将返回列中 'substring' 第一次 出现的索引.STUFF 然后用 'replacement' 替换这个出现.

CHARINDEX('substring', col) will return the index of the first occurrence of 'substring' in the column. STUFF then replaces this occurrence with 'replacement'.

这篇关于替换 SQL 中字符串中第一次出现的子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！