CODEWITHSUNDEEP
sql-servercollationcase-insensitiveexact-match
Ilak
Ilak

Reputation: 158

How to match exact word case insensitive in collation set SQL Server database

I have the following collation set in my database:

COLLATE SQL_Latin1_General_CP1_CS_AS

I want to match an exact word - case insensitive. I am using the following query but it is not working.

String sql = "UPDATE BODYCONTENT SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(max)), ? , ?) as ntext) WHERE BODY like '%[^a-z0-9]' + ? + '[^a-z0-9]%' OR" + 
            " BODY like ? + '[^a-z0-9]%' OR" + 
            " BODY like '%[^a-z0-9]' + ? OR" + 
            " BODY like ?";

When I use something similar in another database where collation is not set, the results are as expected:

select * 
from dbo.persons 
where LastName like '%[^a-z0-9]Dan[^a-z0-9]%' 
   or LastName like 'Dan[^a-z0-9]%' 
   or LastName like '%[^a-z0-9]Dan' 
   or LastName like 'Dan';

Please let me know how to handle the database where the collation is set.

How to match for an exact word - case insensitive.

Example: I want to match these words:

 car
 CAR
 car.
 Car!

I do not want to match these words:

carrot
carrier
car1
Firstcar
1car
carcarcar

The database is SQL Server.

Upvotes: 0

Views: 3274

Answers (3)

pmbAustin
pmbAustin

Reputation: 3980

The easiest way is to simply specify a COLLATION. Here's an example:

SELECT 'True' WHERE 'x' COLLATE Latin1_General_CI_AI = 'X' COLLATE Latin1_General_CI_AI 
SELECT 'True' WHERE 'x' COLLATE Latin1_General_CS_AS = 'X' COLLATE Latin1_General_CS_AS

Note when you run the two statements, only ONE of them returns "True"... the case insensitive version. It doesn't matter what collation your database is either, because you're overriding that collation with an explicit collation.

Using your example from above, it would be something like this:

select * 
from dbo.persons 
where LastName COLLATE Latin1_General_CS_AS like '%Dan%' COLLATE Latin1_General_CS_AS;

This will do a case-sensitive search that will only match where "Dan" is somewhere in the last name.

The COLLATE statement is like a CAST() to a specific collation. So when you're database is by default case insensitive, just use COLLATE to say to interpret the string as a case sensitive collation.

Upvotes: 1

S3S
S3S

Reputation: 25152

This could be done with LIKE and LOWER to return car regardless of case, and car_ where _ is any non alpha-numeric value.

declare @table table (c1 varchar(64))
insert into @table
values
('car'),
('CAR'),
('car.'),
('Car!'),

('carrot'),
('carrier'),
('car1'),
('Firstcar'),
('1car'),
('cars'),    --i added this value to NOT be returned
('carcarcar')

select *
from @table
where 
    lower(c1) = 'car'
    or (lower(c1) like ('car_')
    and right(c1,1) like ('[^0-9]')
    and right(lower(c1),1) like ('[^a-z]'))

Upvotes: 0

Xedni
Xedni

Reputation: 4715

Here's a couple ideas

  1. Collate the column you're comparing with a case-insensitive collation instead. You could do this at runtime, or create a separate computed column which is case insensitive.
  2. Cast the string you want to pattern match against as lower case (LOWER()) first, so you don't have to worry about mixed casing
  3. For your wildcard expression, instead of just having [a-z] have [a-zA-Z]

Upvotes: 0

Related Questions