Searching for 'special' characters in a database using TSQL
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.