Replace occurrences of a list of strings in a string without a cursor in T-SQL

Question

Boy, that's a mouthful...

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.

Ex.

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

Answer 1

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;

Which returns:

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