Ghost characters...
I've got a join which compares many columns within a table against another table. The problem is that there are two columns in the first table which are behaving... badly. Both are varchar fields. For some records this field has a value in it (one-digit number or letter), and for others it is supposedly blank.
I can test one of these blank fields with things like:
ISNULL(filter6)
LEN(filter6) = 0
filter6 = ''
filter6 = ' '
And none of those evaluate to true. However the field is supposedly blank. I copy and paste it into a text file and there's nothing there. I tested it using SELECT LEN(filter6) and got a value of 1. I want to set this value to an empty string, so that it won't screw up my joins, however for the life of me I have no idea what the hell this thing is. For reference the data in this table comes from a bcp'd text file with fixed-length fields using a format file. None of the other columns have any problems; weather they're left empty or not... just filter6 and filter7. I've also tried rtrim(ltrim()) on it to see if I can pull out any leading/trailing spaces and opening the original file in either notepad or vi to see if I can find out where the hell this came from... nothing.
So, I tried to convert it a bit to see what I could get out of it.
SELECT CONVERT(INT, filter6) FROM table
Returns:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '.' to a column of data type int.
But as you can probably guess, trying to update on a value of '.' doesn't really pan out either. Any ideas?
I can test one of these blank fields with things like:
ISNULL(filter6)
LEN(filter6) = 0
filter6 = ''
filter6 = ' '
And none of those evaluate to true. However the field is supposedly blank. I copy and paste it into a text file and there's nothing there. I tested it using SELECT LEN(filter6) and got a value of 1. I want to set this value to an empty string, so that it won't screw up my joins, however for the life of me I have no idea what the hell this thing is. For reference the data in this table comes from a bcp'd text file with fixed-length fields using a format file. None of the other columns have any problems; weather they're left empty or not... just filter6 and filter7. I've also tried rtrim(ltrim()) on it to see if I can pull out any leading/trailing spaces and opening the original file in either notepad or vi to see if I can find out where the hell this came from... nothing.
So, I tried to convert it a bit to see what I could get out of it.
SELECT CONVERT(INT, filter6) FROM table
Returns:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '.' to a column of data type int.
But as you can probably guess, trying to update on a value of '.' doesn't really pan out either. Any ideas?
