Remove Non Printable Characters from a String

The SQL Script below can be used to remove non-printable characters from a string such as CRLF etc.

— Create a Table to store the strings with non printable ASCII Characters
CREATE TABLE ##NoPrintableStrings
(
BadStrings VARCHAR(20)
)

GO

–Insert some strings with non printable ASCII Characters into the table created
INSERT ##NoPrintableStrings SELECT ‘The quick ‘ + CHAR(10) + ‘ “brown’
INSERT ##NoPrintableStrings SELECT ‘fox jumped ‘ + CHAR(11) + ‘ ‘
INSERT ##NoPrintableStrings SELECT CHAR(12) + ‘ over the ‘
INSERT ##NoPrintableStrings SELECT ‘ log ‘ + CHAR(13)
INSERT ##NoPrintableStrings SELECT ‘O”Keefe’
— Show the data in the table
SELECT badstrings FROM ##NoPrintableStrings

/*

Returns:

The quick
“brown
fox jumped
over the
log

O’Keefe

*/
— Update the table to remove any non alphanumerice characters using the wildcard
— functioanlity of the PATINDEX function
UPDATE  ##NoPrintableStrings
SET badstrings = REPLACE(badstrings, SUBSTRING(badstrings, PATINDEX(‘%[^a-zA-Z0-9 ”””]%’, badstrings), 1), ”)
WHERE PATINDEX(‘%[^a-zA-Z0-9 ”””]%’, badstrings) <> 0
— Show that the non alphanumerice characters have been removed
SELECT badstrings FROM ##NoPrintableStrings

/*

Returns:

The quick  “brown
fox jumped
over the
log
O’Keefe

*/