How do I perform a case-sensitive search using LIKE?

Question

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?

Solution 1

Try using COLLATE Latin1_General_BIN rather than COLLATE Latin1_General_CS_AS

Solution 2

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

Solution 3

I use the following:

SELECT COUNT(*)
FROM Person.Person
WHERE LastName COLLATE Latin1_General_CS_AS != upper(LastName) COLLATE Latin1_General_CS_AS

Comments

[Hand-E-Food]

Thanks for all the research!

[Tk1993]

This is what i was looking for. Thanks for this