Hidden characters in varchar column?

[sgk17]

I'm trying to take text from a varchar column and convert it to numeric. However, there are instances where it throws an error and says it's unable to convert varchar to numeric. I know which record it's not working on and when I open the table to look at it, it looks the same as a record that it did work on. I copied the field from SQL Query Analyzer, copied into TextPad and turned on the function in TextPad that shows carriage returns. When I do that I get something like this: (110 is the data and P is the carriage return)
110.. . . . P
When I did the same thing on a record that's visually the same, but actually converted I got:
110. . . . . P

Notice the one that doesn't work had two dots close together. Can SQL Server store "hidden" data that would give me problems when I try to convert to numeric? Thanks in advance.

 

[ClaireHCM]

you can use replace to elimite those empty string

select replace(Col1,' ','') from table.

 

[ArtieChoke]

Sure can. From the char definition in help:

"A char or varchar object can contain any character in the SQL Server code page".

Try pasting that line into an editor that can show hex values and you'll be able to see what's lurking in the data.


"I think we're all Bozos on this bus!

 

[sgk17]

OK. There is a hex value of a0 tacked onto the end of the one that doesn't work. That's 160 in decimal. How do I find out what key on the keyboard that maps to?

 

[ArtieChoke]

That's a strange one. If it's standard ASCII, anything above 126 is not directly tied to the keyboard.

The curious thing about it is if it was 0a instead of a0, it would be a linefeed character. Does the hex editor show the values from left to right or in reverse?

"I think we're all Bozos on this bus!

 

[sgk17]

No, it's an a0. I have some 0a's too. I've found that a0 is a "no break space" or an "underline space". How it's created or what it means I have no idea. When I look at that particular record through the application that created it everything looks normal. Anyway, now I'm just curious to see if anyone has the slightest clue what's going on.

 

[sgk17]

Does anyone know how this character is being inserted into the database? It's a FoxPro app tied to a MS SQL database backend. Here's the problem though. When I use the isnumeric function to see if it's numeric it returns a true. The len function also does not see it. But when I do a cast as numeric it sees it and throws an error that it can't be converted to numeric.

 

[tlbroadbent]

I don't have an answer about where the spurious characters originate. As noted by ClaireHsu, you can use the Replace function to replace the offending character(s). Use the CHAR function to convert the ASCII integer value to a character.

Convert(numeric, Replace(ColName,[COLOR=green]CHAR(160)[/color],''))

You may even want to create a trigger to remove the offending characters when rows are inserted into the database.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm