How Do I Perform a Case-Sensitive Search Using Like?
I'm Trying to Find Records That Contain a String of 6 or More Alpha-Numeric Characters in Uppercase. Some Examples: Pending 3Rdparty Code27 I'm Using the...
I'm trying to find records that contain a string of 6 or more alpha-numeric characters in uppercase. Some examples:
PENDING 3RDPARTY CODE27
I'm using the following statement:
SELECT Details
FROM MyTable
WHERE Details LIKE '%[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]%';
This is returning all records that contain any 6-or-more-letter word, regardless of case.
I've added a COLLATE statement:
SELECT Details
FROM MyTable
WHERE Details COLLATE Latin1_General_CS_AS LIKE '%[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]%';
This changes nothing. It still returns records with 6-or-more-letter word, regardless of case.
Just as a test, I tried:
SELECT Details
FROM MyTable
WHERE Details COLLATE Latin1_General_CS_AS LIKE '%pending%';
SELECT Details
FROM MyTable
WHERE Details COLLATE Latin1_General_CS_AS LIKE '%PENDING%';
Both of these worked, returning records containing "pending" and "PENDING" respectively. So the issue seems to by the LIKE claus's pattern matching.
What can I do to perform this case-sensitive search?
3 Answers
Try using COLLATE Latin1_General_BIN rather than COLLATE Latin1_General_CS_AS
Update due to @GeraldSv: Use collation Latin1_General_BIN
SELECT Details
FROM MyTable
WHERE Details
LIKE '%[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]%'
COLLATE Latin1_General_BIN;
You need to place the collation specifier after the string to be matched rather than the column:
SELECT Details
FROM MyTable
WHERE Details
LIKE '%[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]%'
COLLATE Latin1_General_CS_AS;
Update: While my answer above is correct, there is a bug filed at Connect: Case-SENSITIVITY doesn't work when using a range in like with COLLATE Latin1_General_CS_AS which Microsoft have marked as 'By Design".
I verified by using AdventureWorks2008R2 (case insensitive, out of the box default), in the Person.Person table I changed 3 last names ending in 'n' to 'N', and then ran the following queries:
SELECT COUNT(*)
FROM Person.Person
WHERE LastName LIKE '%N' COLLATE Latin1_General_CS_AS
Success. Return 3 rows as expected.
SELECT COUNT(*)
FROM Person.Person
WHERE LastName LIKE '%[N]' COLLATE Latin1_General_CS_AS
Success. Return 3 rows as expected.
SELECT COUNT(*)
FROM Person.Person
WHERE LastName LIKE '%[N-N]' COLLATE Latin1_General_CS_AS
Success. Return 3 rows as expected.
SELECT COUNT(*)
FROM Person.Person
WHERE LastName LIKE '%[M-N]' COLLATE Latin1_General_CS_AS
Fails. Returns 3334 Rows (which is all Lastname's ending in 'n' and 'N')
Update: Thanks to @GeraldSv, this works:
SELECT COUNT(*)
FROM Person.Person
WHERE LastName LIKE '%[M-N]' COLLATE Latin1_General_BIN
I use the following:
SELECT COUNT(*)
FROM Person.Person
WHERE LastName COLLATE Latin1_General_CS_AS != upper(LastName) COLLATE Latin1_General_CS_AS
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
