Compare data between different collations

Today I was trying to join two fields from different tables. One of the table in the query was from a linked sql server with a different collation. As such when runing this comparision I was getting the error Cannot resolve the collation conflict between “Latin1_General_BIN” and “Latin1_General_CI_AS” in the equal to operation. I am sure many of us would have got this error from time to time. The way I resolved this was simple. Use collate database_default in the comparision statement.

Hence you statement will look like this:

Select * From table1, table2

Where x = y

AND (table1.departmentid =  left(table2.dept,2) collate database_default)

7 Responses to “Compare data between different collations”

  1. Kat Miner Says:

    Thank you for posting. I used it in my query and it worked great.

  2. Laurent Says:

    Great post which solved my problem🙂

  3. Bill Bartmann Says:

    I don’t usually reply to posts but I will in this case, great info…I will add a backlink and bookmark your site. Keep up the good work!

  4. Darshan Shah Says:

    select * from dbo.TempCh where [customer name] like ‘上海精元机械有限公司’ collate Chinese_PRC_CI_AI_KS_WS

    I am facing issue while comparision

  5. leandro sardinha Says:

    I used this statement and it really saved my day… Thanks a lot, still posting

  6. Don Says:

    Thanks for the tip, it saved me much time and effort.

  7. Sandy Says:

    Very useful mate.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: