CODEWITHSUNDEEP
sql-serversql-server-2008collationdatabase-schema
Tanvir Kaiser
Tanvir Kaiser

Reputation: 253

Collation Problem

Im getting different collation in different objects in a database...

Which is creating problem while executing query. Can someone help me out?

Thanks In Advance

Upvotes: 1

Views: 1616

Answers (1)

Filip De Vos
Filip De Vos

Reputation: 11908

You will have to specify according to which collation you wish to do the join (or where clause)

See the example below on how to code this.

create table x( id int, name varchar(256) collate SQL_SwedishStd_Pref_CP1_CI_AS)
create table y( id int, name varchar(256) collate SQL_Latin1_General_CP1_CI_AS)

insert into x values (1, 'Filip')
insert into y values (1, 'Filip')

So if you wish to join based on Latin1 collation you write the query like this:

select * 
  from x
  join y 
    on x.name collate SQL_Latin1_General_CP1_CI_AS = y.name

If you wish to join based on Swedish collation you write the query like this:

select * 
  from x
  join y 
    on x.name = y.name collate SQL_SwedishStd_Pref_CP1_CI_AS

If the collation difference has been created by incorrectly defining the table you can alter the column to switch the collation:

alter table x alter column name varchar(256) collate SQL_Latin1_General_CP1_CI_AS

If you then run the query like this, no more errors will occur:

select * 
  from x
  join y 
    on x.name = y.name

Upvotes: 3

Related Questions