CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Lift
Lift

Reputation: 546

SQL Like query is case sensitive even with collation of CI_AI

Query with no results

I'm at a loss here. I run a like query and it works, but it's case sensitive even though the collation is set to CI_AI which to my knowledge is case insensitive.

I've looked around for similar issues but all the answers are change the collation to CI which it already is.

Any ideas what I've done wrong?

Upvotes: 2

Views: 778

Answers (3)

Lift
Lift

Reputation: 546

Found the answer for anyone else who finds themselves in the same situation as me. Greg was right, it was the column being CS even though the table and database was set to CI.

I had made the table through visual studio and when I reviewed the script it had COLLATE Latin1_General_CS_AS

Redefined the columns without it so it was the table default and all works now.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You are using SQL Server. Why not just do this?

where firstname like '%[dD]%'

Upvotes: 0

ViKiNG
ViKiNG

Reputation: 1334

Try something like:

SELECT TOP 100 *
FROM dbo.PersonDetails
WHERE FirstName COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%d%' COLLATE SQL_Latin1_General_CP1_CS_AS;

Upvotes: 2

Related Questions