SQL Server LIKE operator with character range and case sensitive column

Question

Can you explain me why following query returns rows which does not fulfill LIKE condition. Column in the table has case sensitive collation, so query shouldn't return anything.

CREATE TABLE #temp (col CHAR COLLATE SQL_Latin1_General_CP1_CS_AS);
INSERT INTO #temp VALUES ('A'), ('B'), ('C'), ('D'), ('E');
SELECT * FROM #temp WHERE col LIKE '[b-d]';

Any other case sensitive collation (ie. Latin1_General_100_CS_AS, Polish_100_CS_AS, Modern_Spanish_100_CS_AS) also doesn't work correctly.

As far as I can found only binary sort order collections (Latin1_General_100_BIN2, Modern_Spanish_100_BIN2) works well.

This error exist only when I use range in LIKE condition. When I change [b-d] to [bcd] everything is OK.

Is it a bug in database engine?

I use Microsoft SQL Server 2008 (SP3) - 10.0.5846.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Answer 1

No it's not a bug.

The range in the pattern syntax is not a regular expression. It defines a range under the collation sort order.

Those collations sort AaBbCcDd...YyZz (with upper case and lower case intermingled) so this is an expected result.

Binary collations sort AB .... YZ .... ab ... yz (with upper case and lower case separated) which is why it works correctly for those.