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. Within the LIKE phrase we have allowed all 26 letters of the alphabet (both capital and small) as well as the space character. Here is the code.

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 

Collate

Do we need collate in the above statement?