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]

Re: Latin1_General_ BIN comparing two char strings

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]

Re: Latin1_General_ BIN comparing two char strings

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_Gene ral_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]

Re: Latin1_General_ BIN comparing two char strings

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_Gene ral_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