One of my colleagues asked me for some quick SQL help earlier. Some data in the database he was looking at had ended up with a 'special character' on the end of it in a specific table. This didn't affect all rows, just some, but he needed helping identify those rows.

For the sake of arguments, lets imagine that the table was called Item, and the affected column was named Name, and there was also an Id column named ItemId. He knew one of the affected Items, and so we examined the data to determine what character it was at the end. To do this we did

SELECT ASCII(SUBSTRING(Name,10,1))
FROM Item
WHERE ItemId = 100

This informed us that the character was actually ASCII value 160. Knowing this, I then used the PATINDEX function and produced a query like:

SELECT *
FROM Item
WHERE PATINDEX('%' + CHAR(160) + '%',Name) > 0

which seemed to return the row that he'd already identified along with new ones. This is probably not the most performant method, but this was needed to check something in a debugging/support environment and not code that would be run on a regular basis.