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

Question

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.

Answer 1

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.