Case sensitivity not working

Question

I have an issue where I keep getting the value 'CtP_PETER_Fact' out of the query below. It should be a case sensitive where-clause. I tried it in a few different ways: setting the COLLATE statement after 'Where ObjectName' in the select, after the regular expression and creating the column with the collation. I keep getting the output I would not expect. Is it some issue with my regular expression maybe? I did a lot of experimenting with the regular expression as well, but can't seem to get it to work.

IF OBJECT_ID('tempdb..#nameFacts') IS NOT NULL
DROP TABLE #nameFacts;


CREATE TABLE #nameFacts (


    objectname varchar(200) COLLATE SQL_Latin1_General_CP1_CS_AS,
    ObjectType varchar(40)

)


insert into #nameFacts (objectname, ObjectType)
values
 ('BPD_Inslap_Fact','Fact')
,('CTP_HENK_FACT','Fact')
,('CTP_PETER_Fact','Fact')
,('CTP_PETER_FACT','Fact')
,('CtP_PETER_Fact','Fact')
,('C0P_PETER_Fact','Fact')
,('C0P_PETER_FACT','FACT')


SELECT *
FROM #nameFacts
WHERE
ObjectName --COLLATE SQL_Latin1_General_CP1_CS_AS 
            LIKE '[A-Z0-9][A-Z][A-Z][_][A-Z][A-Z][A-Z][A-Z][A-Z][_][F][a][c][t]' --COLLATE SQL_Latin1_General_CP1_CS_AS


IF OBJECT_ID('tempdb..#nameFacts') IS NOT NULL
DROP TABLE #nameFacts;

I keep getting the output below, where I would not expect the value 'CtP_PETER_Fact'. I am on SQL Server 2016 SP2 CU 17.

Answer 1

[A-Z] is a range of characters. The characters in the range depend on the sort order defined by the collation:

SELECT 
    SV.number, 
    CHAR(SV.number) 
FROM master.dbo.spt_values AS SV
WHERE 
    SV.[type] = 'P'
    AND SV.number BETWEEN 1 AND 255
    AND CHAR(SV.number) COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '[A-Z]'
ORDER BY 
    CHAR(SV.number) COLLATE SQL_Latin1_General_CP1_CS_AS ASC;

db<>fiddle online demo

Partial output:

number	(No column name)
65	A
97	a
193	Á
225	á
224	à
192	À
194	Â
226	â
196	Ä
228	ä
...	...

As you can see, many characters you might not expect are defined to sort within that range.

To match just certain uppercase alphas, you would need:

[ABCDEFGHIJKLMNOPQRSTUVWXYZ]

Or use a collation where the sort order for the range A-Z matches your requirements, e.g. Latin1_General_100_BIN2.

The case-sensitive or case-insensitive part of a collation only affects equality comparisons. LIKE '[A]' would match both 'a' and 'A' under a case-insensitive collation, but only 'A' with a case-sensitive collation.