Believe half of what you see, and none of what Studio Express grid view tells you about whitespace

So I am cleaning up some data from an old MS SQL database today. I needed to split a column on what appeared to be a space character. But obviously not because SELECT CHARINDEX( char(32), MyColumn ) returned zero.

No problem, I thought. It is probably a tab character or something. So I decided to check the ascii values. In a less than brilliant moment, I decided to copy and paste one of the values from grid view and use it for testing. Imagine my surprise when SELECT ASCII( SUBSTRING('some value', 5, 1) ) told me the character in question really was a space. Oookaay... then why does SELECT CHARINDEX( char(32), MyColumn ) return zero?

It took me a few minutes before I realized the problem. The data actually contained char(13) + char(10), but Studio Express grid view helpfully translated the new lines into spaces for my viewing pleasure. That is what I get for not checking the underlying data with sql.

Really I know better. When it comes to whitespace, never trust the graphical tools. They all lie.