What's That Confounded Character?? (ASCII and CHAR Functions)

I was on my way back from the kitchen at my client's site when I passed the office of one of their developers. We made momentary eye contact and I could see that look of desperation. She immediately called out my name and said something like, "Dave! I need your help. You're smart." I can't resist that kind of flattery so I immediately put down my yogurt and pulled up a chair.

It seemed like a simple problem. She just wanted to take part of a string from a larger one - a substring. Below are examples of the complete strings. They were created from some kind of a flat file extract from a legacy system. Assume the column name is PRODVAL.

ymkez 11/30/2009 456.8
nipin+ 11/30/2009 432.90
gapter- 11/30/2009 543.12

She simply wanted the first part of the string - up to the "space". I don't recall her logic at this point but it used the T-SQL SUBSTRING and PATINDEX functions.

I began to re-write her query. I came up with the following.
    SELECT SUBSTRING(PRODVAL, 1, CHARINDEX(' ', PRODVAL) - 1) FROM table

Well, it didn't work. To troubleshoot the problem I rewrote the above query to as follows.
     SELECT CHARINDEX(' ', PRODVAL) FROM table

The query returned a whole long list of zeros. Something was obviously wrong. I could see the space. So why couldn't the CHRINDEX function? Next step, figure out what the "space" character actually is. The next query helped determine that.
    SELECT ASCII(SUBSTRING(PRODVAL,6,1) FROM table

The first row in the table had a PRODVAL value with a "space" in the 6th position so the above query returned the ASCII value of this 6th character. It returned a 9 - Tab.

Ok great. So now I knew we were looking for a  tab, not a space. So, going back to our original query, we can modify it to use the CHAR function to look for the tab.
    SELECT SUBSTRING(PRODVAL, 1, CHARINDEX(CHAR(9), PRODVAL) - 1) FROM table

Voila! Using the ASCII function we identified the mysterious character and then used the CHAR function to convert the ASCII code for Tab (9) to a character so that we could use it in a SUBSTRING function.