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 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