CODEWITHSUNDEEP
sqlsql-servert-sql
Lars Kemmann
Lars Kemmann

Reputation: 5674

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

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?

Upvotes: 1

Views: 162

Answers (2)

Lars Kemmann
Lars Kemmann

Reputation: 5674

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.

Upvotes: 1

Darrin Cullop
Darrin Cullop

Reputation: 1210

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.

Upvotes: 1

Related Questions