SQL Server Finding Bad Characters

In SQL Server you can run a SELECT query that shows you all of the records that have “bad” or “unacceptable” characters anywhere in a column or columns. This is a form of data validation. Another example this is when you need to run a SELECT query that is case-sensitive or accent-sensitive. First we will look at the query that shows you all of the rows with bad characters.

Suppose you have a table called Persons that has three columns in it: FirstName, Amount and Number. In the FirstName column you need to know if there are any bad characters in the column. Here is part of a stored procedure.

PRINT 'Outputting records with bad characters in the FirstName column...'
SELECT FirstName, Amount, Number FROM dbo.New WHERE FirstName 
		LIKE '%[^A-Za-z0-9% ]%' COLLATE Latin1_General_BIN;
PRINT 'Finished checking for bad characters.'
-- "Bad" characters are characters that are not (^) in the list provided in the square brackets 

Leave a comment

Your email address will not be published. Required fields are marked *