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.