0

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.

the output

1 Answer 1

5

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.