CODEWITHSUNDEEP
sql-serversql-server-2008collationsql-like
Santiago
Santiago

Reputation: 2320

SQL-Server is ignoring my COLLATION when I'm using LIKE operator

I'm working with Spanish database so when I'm looking for and "aeiou" I can also get "áéíóú" or "AEIOU" or "ÁÉÍÓÚ", in a where clause like this:

SELECT * FROM myTable WHERE stringData like '%perez%'

I'm expencting:

* perez
* PEREZ
* Pérez
* PÉREZ

So I changed my database to collation: Modern_Spanish_CI_AI

And I get only:

* perez
* PEREZ

But if I do:

SELECT * FROM myTable WHERE stringData like '%perez%' COLLATE Modern_Spanish_CI_AI

I get all results OK, so my question is, why if my database is COLLATE Modern_Spanish_CI_AI I have to set the same collation to my query???

I'm using SQL-Server 2008

Upvotes: 5

Views: 12180

Answers (2)

John Woo
John Woo

Reputation: 263723

You can use COLLATE, eg.

SELECT * 
FROM TableName
WHERE strData COLLATE Latin1_general_CI_AI = 'perez' COLLATE Latin1_general_CI_AI

both sides must have the same collation.

Others:

Upvotes: 10

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

You need to change the collation of the table COLUMN itself.

select collation_name, *
from sys.columns
where object_id = object_id('tblname')
  and name = 'stringdata';

If you're lucky it is as easy as (example)

alter table tblname alter column stringdata varchar(20) collate Modern_Spanish_CI_AS

But if you have constraints and/or schema bound references, it can get complicated.
It can be very difficult to work with a database with mixed collations, so you may want to re-collate all the table columns.

Upvotes: 4

Related Questions