CREATE FUNCTION StripNonAlphaNumeric(@OriginalText VARCHAR(8000))
RETURNS VARCHAR(8000)  
BEGIN 
DECLARE @CleanedText VARCHAR(8000) 
;WITH tally (N) as
(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id)
 FROM Master.dbo.SysColumns sc1
 CROSS JOIN Master.dbo.SysColumns sc2)
SELECT @CleanedText = ISNULL(@CleanedText,'') +  
CASE 
  --ascii numbers are 48(for '0') thru 57 (for '9')
  WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 48 AND  57  
  THEN SUBSTRING(@OriginalText,Tally.N,1) 
  --ascii upper case letters A-Z is 65 thru 90
  WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 65 AND  90  
  THEN SUBSTRING(@OriginalText,Tally.N,1) 
  --ascii lower case letters a-z is 97 thru 122
  WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 97 AND  122  
  THEN SUBSTRING(@OriginalText,Tally.N,1) 
  ELSE '' END
      
FROM tally           WHERE Tally.N <= LEN(@OriginalText)            
                
RETURN @CleanedText 
END

CREATE FUNCTION dbo.RemoveSpecifiedCharsFromString (
    @string varchar(2000),
    @charsToRemove varchar(50)
    )
RETURNS varchar(2000)
AS
BEGIN
WHILE PATINDEX('%[' + @charsToRemove + ']%', @string) > 0
    SET @string = STUFF(@string, PATINDEX('%[' + @charsToRemove + ']%', @string), 1, '')
RETURN @string
END --FUNCTION



DECLARE @string varchar(2000)
DECLARE @charsToRemove varchar(50)
SET @charsToRemove = CHAR(00) + CHAR(01) + CHAR(02) + CHAR(03) + CHAR(04) + CHAR(05) + /*... + */
    CHAR(09) + CHAR(10) + /* ... + */ CHAR(13) + /* ... + */ CHAR(31)
    
SELECT string, dbo.RemoveSpecifiedCharsFromString(string, @charsToRemove)
FROM (
    SELECT 'abc' + CHAR(10) + CHAR(13) + CHAR(01) + 'def' AS string UNION ALL
    SELECT 'ghi' + CHAR(03) + CHAR(04) + REPLICATE(CHAR(05), 10) + 'jkl'
) AS test_data

 CREATE FUNCTION dbo.DropControlCharacters 
        (@pString VARCHAR(8000))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN 
 SELECT CleanedString = 
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        @pString
        ,CHAR(0),'') COLLATE Latin1_General_BIN
        ,CHAR(1),'') COLLATE Latin1_General_BIN
        ,CHAR(2),'') COLLATE Latin1_General_BIN
        ,CHAR(3),'') COLLATE Latin1_General_BIN
        ,CHAR(4),'') COLLATE Latin1_General_BIN
        ,CHAR(5),'') COLLATE Latin1_General_BIN
        ,CHAR(6),'') COLLATE Latin1_General_BIN
        ,CHAR(7),'') COLLATE Latin1_General_BIN
        ,CHAR(8),'') COLLATE Latin1_General_BIN
        ,CHAR(9),'') COLLATE Latin1_General_BIN
        ,CHAR(10),'') COLLATE Latin1_General_BIN
        ,CHAR(11),'') COLLATE Latin1_General_BIN
        ,CHAR(12),'') COLLATE Latin1_General_BIN
        ,CHAR(13),'') COLLATE Latin1_General_BIN
        ,CHAR(14),'') COLLATE Latin1_General_BIN
        ,CHAR(15),'') COLLATE Latin1_General_BIN
        ,CHAR(16),'') COLLATE Latin1_General_BIN
        ,CHAR(17),'') COLLATE Latin1_General_BIN
        ,CHAR(18),'') COLLATE Latin1_General_BIN
        ,CHAR(19),'') COLLATE Latin1_General_BIN
        ,CHAR(20),'') COLLATE Latin1_General_BIN
        ,CHAR(21),'') COLLATE Latin1_General_BIN
        ,CHAR(22),'') COLLATE Latin1_General_BIN
        ,CHAR(23),'') COLLATE Latin1_General_BIN
        ,CHAR(24),'') COLLATE Latin1_General_BIN
        ,CHAR(25),'') COLLATE Latin1_General_BIN
        ,CHAR(26),'') COLLATE Latin1_General_BIN
        ,CHAR(27),'') COLLATE Latin1_General_BIN
        ,CHAR(28),'') COLLATE Latin1_General_BIN
        ,CHAR(29),'') COLLATE Latin1_General_BIN
        ,CHAR(30),'') COLLATE Latin1_General_BIN
        ,CHAR(31),'') COLLATE Latin1_General_BIN
        ,CHAR(127),'') COLLATE Latin1_General_BIN
;
GO

--===== Conditionally drop the test table to make reruns in SSMS easier.
     IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
        DROP TABLE #TestTable
;
--===== Create and populate the test table with test data.
     -- Most rows will have 2 embedded control characters although some may have just 1
     -- just due to random selection.
 SELECT TOP 100000
        RowNum     = IDENTITY(INT,1,1),
        SomeString = 
            STUFF(
                STUFF(
                    '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz`~!@#$%^&*()_-+={[}]|\:;"''<,>.?/',
                ABS(CHECKSUM(NEWID()))%94+1, 1, CHAR(ABS(CHECKSUM(NEWID()))%30+1)),
            ABS(CHECKSUM(NEWID()))%94+1, 1, CHAR(ABS(CHECKSUM(NEWID()))%30+1))
   INTO #TestTable
   FROM master.sys.all_columns ac1
  CROSS JOIN master.sys.all_columns ac2
;
GO

 SELECT ca.CleanedString
   FROM #TestTable tt
  CROSS APPLY dbo.DropControlCharacters(tt.SomeString) ca
;