sql - 在 T-SQL 中替换没有游标的字符串中出现的字符串列表

Question

天哪，这真是一口……

我想从字符串中解析标记。标记可以是单词或短语，我想要的是将任何标记的每次出现替换为字符串。我想在不使用光标的情况下执行此操作。

例如。

declare @str varchar(256) = 'I want this type to be left and of type to be gone. the and a should also be gone of course remains'

create table #Tokens (token varchar(50))
go

insert table (token) values ('of type')
insert table (token) values ('a')
insert table (token) values ('the')
insert table (token) values ('to')
insert table (token) values ('of')
go

我想要的是一个内联函数，它将用“”(空字符串)替换字符串中找到的任何标记列表。

Answer 1

一个非常简单的答案是使用以下内容:

USE tempdb;

DECLARE @str VARCHAR(256);

SET @str = 'I want this type to be left and of type to be gone.
           the and a should also be gone of course remains';

CREATE TABLE #Tokens (token VARCHAR(50));
INSERT INTO #Tokens (token) VALUES ('of type');
INSERT INTO #Tokens (token) VALUES ('a');
INSERT INTO #Tokens (token) VALUES ('the');
INSERT INTO #Tokens (token) VALUES ('to');
INSERT INTO #Tokens (token) VALUES ('of');

SELECT @str = REPLACE(@str, token, '')
FROM #Tokens;

SELECT @str;

DROP TABLE #Tokens;

返回结果:

I wnt this type  be left nd   be gone.  nd  should lso be gone  course remins