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?

+26


source to share


3 answers


Try to use COLLATE Latin1_General_BIN

and notCOLLATE Latin1_General_CS_AS



+38


source


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

      

+26


source


I am using the following:

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

      

0


source







All Articles