在 T-SQL 中替换没有游标的字符串中出现的字符串列表 [英] Replace occurrences of a list of strings in a string without a cursor in T-SQL

问题描述

男孩，那是一口……

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

I want to parse tokens out of a string. The tokens can be a word or a phrase and what I want is to replace each occurrence of any of the tokens with a string. I'd like to do this without using a cursor.

例如

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

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

what I want is an inline function that will replace any of the list of tokens found in the string with '' (empty string).

推荐答案

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

A very simple answer would be to use the following:

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

这篇关于在 T-SQL 中替换没有游标的字符串中出现的字符串列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！