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

Other Methods
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.

Tally Method

/*
SELECT dbo.SSC_fn_TallyClean('This will remove all of this---->!)#%*)^+__#@#$+_!)#~!!! <----.','[a-zA-Z0-9. ,]') Cleaned
*/ 
CREATE FUNCTION SSC_fn_TallyClean(
                @A varchar(500),
                @Rep varchar(100))
RETURNS varchar(500)
AS
BEGIN
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)
FROM Tally
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.
END