How do I perform a case sensitive search using LIKE?
I am trying to find records that contain a string of 6 or more uppercase alphanumeric characters. Some examples:
PENDING 3RDPARTY CODE27
I am using the following instruction:
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 returns all records containing any 6 or more alphabetic word, no matter the case.
I added an operator COLLATE
:
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]%';
It does not change anything. It still returns records with 6 or more letter words, regardless of the case.
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 by returning records containing "pending" and "PENDING" respectively. So the problem is with LIKE
claus pattern matching .
What can I do to perform this case sensitive search?
source to share
Update due to @GeraldSv : use mappingLatin1_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 put the sort specifier after matching the string, not 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. Even though my answer is above, there is a bug related to Connect: Case-SENSITIVITY does not work when using a range such as COLLATE Latin1_General_CS_AS which Microsoft has labeled "By Design".
I checked with AdventureWorks2008R2 (case insensitive, out of field by default), in 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 lines as expected.
SELECT COUNT(*)
FROM Person.Person
WHERE LastName LIKE '%[N]' COLLATE Latin1_General_CS_AS
Success. Return 3 lines as expected.
SELECT COUNT(*)
FROM Person.Person
WHERE LastName LIKE '%[N-N]' COLLATE Latin1_General_CS_AS
Success. Return 3 lines as expected.
SELECT COUNT(*)
FROM Person.Person
WHERE LastName LIKE '%[M-N]' COLLATE Latin1_General_CS_AS
It fails. Returns 3334 lines (that's all the last name 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
source to share