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