How does SQL Server Wildcard Character Range, eg [A-D], work with Case-sensitive Collation?
You need a binary collation as indicated in Md. Elias Hossain's answer.
The explanation is that ranges in the pattern syntax work off Collation sort order rules.
In range searches, the characters included in the range may vary depending on the sorting rules of the collation.
So
;WITH T(C) AS(SELECT 'A' UNION ALLSELECT 'B' UNION ALLSELECT 'C' UNION ALLSELECT 'D' UNION ALLselect 'a' union allselect 'b' union allselect 'c' union allselect 'd')SELECT *FROM TORDER BY C COLLATE Latin1_General_CS_ASReturns
C----aAbBcCdDSo the range A-D excludes a but includes the other 3 lower case letters under a CS collation.
It can be done in either way:
a. Use COLLATE while create the table as:
CREATE TABLE #TEST_LIKE_Patterns( ID INT IDENTITY(1,1), CharColumn VARCHAR(100) COLLATE Latin1_General_BIN);b. Use COLLATE while selecting data as
SELECT *FROM #TEST_LIKE_PatternsWHERE CharColumn LIKE '%[A-D]%' COLLATE Latin1_General_BIN;
try
SELECT *FROM #TEST_LIKE_PatternsWHERE CharColumn LIKE '[A-D]%' COLLATE Latin1_General_BIN;