Replace occurrences of a list of rows in a row without a cursor in T-SQL

Question

Boy, what a sip ...

I want to parse tokens from a string. Tokens can be a word or phrase, and I want to replace each occurrence of any of the tokens with a string. I would like to do this without using a cursor.

Ref.

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 a built-in 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 this:

 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; 

What returns:

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

Answer 2

I use this with a REAL table.

 CREATE FUNCTION [dbo].[funStripSpecialCharacters] ( @inputString as varchar(max) ) RETURNS varchar(max) AS BEGIN select @inputString = REPLACE(@inputString, SpecialCharacter, '') from SpecialCharacters RETURN @inputString END