This is something that most people eventually need for reporting purposes. This function uses a Tally table to ‘clean’ a string, removing anything you don’t specify in the @Rep parameter. In the case below, I remove everything but letters, numbers, spaces, commas and periods. This is a slightly modified version of a function I wrote for something else, so the specifics like replacing a lot of the double spaces, and replacing carriage returns and line breaks with spaces were specifically for that. Remove those pieces of code if you wish, or add those as additional parameters to the function.
I’ll mention that I’ve seen people claim a while loop will beat this method and others that say this method is still faster. I haven’t tested this enough personally to make the claim one way or another, but I prefer this one. This is also one of the accepted places where a CLR function can be superior to T-SQL, but for people who can’t or don’t want to use CLR on their servers, this will still work.
SELECT dbo.SSC_fn_TallyClean('This will remove all of this---->!)#%*)^+__#@#$+_!)#~!!! <----.','[a-zA-Z0-9. ,]') Cleaned
CREATE FUNCTION SSC_fn_TallyClean(
DECLARE @B VARCHAR(500)
SET @B = '' -- Initialize @B
-- Remove Line Feed / Carriage Returns (The tally code would have removed them, but I wanted to replace them with spaces for readability.)
SET @A = REPLACE(REPLACE(@A,CHAR(10),' '),CHAR(13),' ')
SELECT @B = @B + SUBSTRING(@A,N,1)
WHERE N <= DATALENGTH(@A)
AND SUBSTRING(@A,N,1) LIKE @Rep -- Remove everything but letters, numbers, spaces, period and comma.
RETURN REPLACE(REPLACE(@B,' ',' '),' ',' ') -- Removes some double spaces.