Case sensitivity not working

All we need is an easy explanation of the problem, so here it is.

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.

Case sensitivity not working

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply