Why might a string not be equal to a stored varchar?

Question

The following query returns no results:

select *
from MyTable
where MyVarChar = '99903'

MyVarChar is a varchar(20) not null column.

The following data is stored in the table (a la 'select *'):

Id    MyVarChar
1     99901
2     99902
3     99903

Intriguingly, if I supply '99901' or '99902' in the comparison, I get a result.

I've tried ltrim and rtrim on both sides of the comparison with no success.

I'm... confounded. :P Any thoughts?

Are you absolutely sure there isn't a non-printable character within the record for 99903? Like a tab or something... — NotMe, Mar 09 '16 at 22:15
I'm not. But how would I go about testing for that? (Beyond the trim operations?) — Lars Kemmann, Mar 09 '16 at 22:15
You could test that by `SELECT MyVarChar, LEN(MyVarChar) FROM MyTable`. You should have only 5 in the second column of the result. — trincot, Mar 09 '16 at 22:18
If you need to trim Unicode whitespace have a look at [this](http://stackoverflow.com/questions/35245812/whats-a-good-way-to-trim-all-whitespace-characters-from-a-string-in-t-sql-witho/35247507#35247507) answer. — HABO, Mar 09 '16 at 23:11
fyi: [`LEN`](https://msdn.microsoft.com/en-us/library/ms190329.aspx) ignores trailing blanks. Since you've already checked with `RTRIM`, trincot's test is applicable. The alternative to `LEN` is `DATALENGTH` which returns the number of _bytes_. For Unicode strings you can use `DataLength( MyNVarChar ) / DataLength( NChar( 42 ) )` to get the number of characters. — HABO, Mar 10 '16 at 01:49
I'd use `SELECT CAST(MyVarChar as varbinary(max))` to see the actual value in the column. With simple `SELECT` it is hard to notice non-printable characters — Vladimir Baranov, Mar 10 '16 at 01:50

score 1 · Answer 1 · answered Mar 10 '16 at 00:12

Try something like this:

SELECT *
FROM MyTable
WHERE MyVarChar LIKE '%99903%';

That will return anything with 99903 in it, so if you've got extra stuff on the end (or in front), it will still work. RTRIM() and LTRIM() should have fixed that for you though.

score 1 · Accepted Answer · answered Mar 10 '16 at 03:48

1

Doing the cast to varbinary(max) suggested by @Vladimir showed the hidden whitespace: it turned out to be a CR+LF at the end of the string.

answered Mar 10 '16 at 03:48

Lars Kemmann

5,509
3
35
67

Why might a string not be equal to a stored varchar?

2 Answers2