PigRider Blogs

Sign In
HTML CSS Javascript Ruby on Rails C++ Java Python SQL Git Linux Others All

Find records with non-printable characters in T-SQL

[Author: Dingyu]   [Sun, 2013-08-11, 17:44]   [2607 views]

SQL

I know two ways to find records with non-printable characters in T-SQL. The first way, use the "like" statement to do it:
SELECT * FROM tableName
WHERE (columnName COLLATE Latin1_General_BIN2) like '%[^' + CHAR(32) + '-' + CHAR(126) + CHAR(9) + CHAR(10) + CHAR(13) + ']%'
The second way, use the "PATINDEX" function to do it:
DECLARE @sPrintableCharacters VARCHAR(20)
SET @sPrintableCharacters = '%[^' + CHAR(32) + '-' + CHAR(126) + CHAR(9) + CHAR(10) + CHAR(13) + ']%'

SELECT * FROM tableName
WHERE PATINDEX(@sPrintableCharacters, columnName COLLATE Latin1_General_BIN2)>0