Query question

[mrrrl]

I have a database table called patient. Which has a field called account_id [char](12)NULL.

My question is when I use this query and it works.

select last_name from patient
where account_id = ' 12416'

Results :
last_name
------------------
Todd

(1 row(s) affected)

But if I use this query:

select last_name from patient
where account_id = '12416'

Results :
last_name
------------------

(0 row(s) affected)

Why does one work and not the other. Do the spaces get stored in the database? I understand that " 12416" is different than "12416" but does the query only look for the numbers not the spaces?

Thanks

 

[rac2]

Hello,

Yes the spaces are stored and they are part of the string that is compared in the WHERE clause. This is true for the CHAR datatype. A CHAR(12) column will always have 12 characters stored. If you store a string with fewer than 12 characters, SQL Server will fill with trailing spaces. So if you store 'Bob', you will have 'Bob ' in the database and that is what will be matched in the WHERE clause. From your example it appears that the account_id column has right-justified data, that is it has leading spaces instead of trailing spaces. Who knows how that came to be, but you can match just the numbers like this -

select last_name from patient
where LTRIM(account_id) = '12416'

See string functions in Books Online.

 

[mrrrl]

Thanks for the reply, LTRIM worked out just great!

Thanks again.