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)

FROM #nameFacts
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:

FROM master.dbo.spt_values AS SV
    SV.[type] = 'P'
    AND SV.number BETWEEN 1 AND 255
    AND CHAR(SV.number) COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '[A-Z]'
    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:


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 or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply