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?

Answer 1

Try using COLLATE Latin1_General_BIN rather than COLLATE Latin1_General_CS_AS