Remove Characters in String T-SQL with No Loop

Ok, I’ve seen many ways to do this using loops, but nothing in plain T-SQL

Let’s say we have a string that may contain illegal characters, in my case it was project identifiers that needed to be used as file names for exporting. However, many had / or * in the names.

One thing I hate in T-SQL is loops, so having only found looping ways to solve this I started from scratch.

He’s my solution:

Build a table of exclusions, i.e. characters or strings that you want to remove from your string (in my example below this is a temp table, but I would make it a permanent table in the end solution).

Then use REPLACE joined to this table to clean the string e.g.

/*Create the temp table for the exclusion list table*/
CREATE TABLE #ExclusionList
	(
	ExcludedChar VARCHAR(50)
	)

/*Add the stuff we want to exclude*/
INSERT INTO #ExclusionList SELECT '\'
INSERT INTO #ExclusionList SELECT '|'
INSERT INTO #ExclusionList SELECT '?'
INSERT INTO #ExclusionList SELECT '*'
INSERT INTO #ExclusionList SELECT ':'
INSERT INTO #ExclusionList SELECT '<'
INSERT INTO #ExclusionList SELECT '>'
INSERT INTO #ExclusionList SELECT '"'
INSERT INTO #ExclusionList SELECT 'egg'

/*Get the horrible string that we want to clean*/
DECLARE @string VARCHAR(50) = 'th""<i>???s:: :|||"<egg>wo|r|k|**s*< ">we*l::l::'

/*Do the SELECT*/
SELECT
	@string = REPLACE(@string,ExcludedChar,'')
FROM #ExclusionList

/*Show the string post processing*/
SELECT @string

/*Drop the temp table*/
DROP TABLE #ExclusionList

If you run this you’ll see that all the excluded text has been stripped and you’re left with “this works well”.

I’m all ready thinking of ways this could be expanded, e.g. Exclusion List type added to the exclusion list, you could then maintain different lists for different things, e.g. File Names, Email Addresses, etc and just add a WHERE clause to the REPLACE select statement.

Any comments or ideas please let me know!