Latin1_General_BIN comparing two char strings

[igor]

My database is Latin1_General_BIN.
My table is called CUSTOMER.
One column in this table (descr_id) is defined as char(10).

When I query…

SELECT *
FROM CUSTOMER
WHERE descr_id = ‘ABCD’

Result set from above query is:
descr_id
ABCD

I get the same result back as I do when…
SELECT *
FROM CUSTOMER
WHERE descr_id = ‘ABCD ‘ (notice ABCD string has padded spaces).

Result set from above query is:
descr_id
ABCD

Does anyone know why this is the case? I would expect second query to
not to return anything.

Many thanks,
Igor

[Plamen Ratchev]

SQL Server follows the ANSI standard padding rules for character strings,
which requires strings used in comparisons to be padded so that their
lengths match before comparing them.

http://support.microsoft.com/kb/316626

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[igor]

Thanks... lets look at the following example...
When running following query on SQL Server 2005 with
Latin1_General_BIN

SELECT *
FROM CUSTOMER
WHERE descr_id like ‘% ‘ (again, notice padded spaces after wild
card sign)

Result set from above query is:
descr_id
ABCD

...and same query on SQL Server 2000 with SQL_Latin1_General_CP850_BIN
returns nothing.

What is the difference here then? I presume difference is in collation
change, but can’t find any article that speaks about this issue. Any
help would be appreciated.

Thanks,
Igor

[Plamen Ratchev]

I just tested the following code on both SQL Server 2000 and SQL Server 2005
and it returns the same results (the matching row):

CREATE TABLE Foo (
keycol INT PRIMARY KEY,
datacol CHAR(10) COLLATE SQL_Latin1_General_CP850_BIN);

INSERT INTO Foo VALUES(1, 'ABCD');

SELECT datacol
FROM Foo
WHERE datacol LIKE '% ' COLLATE Latin1_General_BIN;

SELECT datacol
FROM Foo
WHERE datacol LIKE '% ';

Can you post example (DDL and sample data) to reproduce your results?

HTH,

Plamen Ratchev
http://www.SQLStudio.com