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.
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.
[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;
|number||(No column name)|
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.
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 🙂