Joining SQL tables with different collation

This will be a very short read but I think someone can find this post useful in the future (probably that will be me) because there are a lot of misguiding answers out there. I’ll show one practical example on where you can hit this TSQL problem and an easy solution on how to resolve it.

simpsons.jpg
* absolutely no clue what image to attach to this post so we might go with this one until better idea comes up *

The problem

We need to retrieve only the from T1 table in the DB1 database and join them with the T2 table  in the DB2 database by the T1.Name and T2.Name with the condition that T1.Name needs to starts with T2 name we need to run this query.

We have two databases with two different collations and we are not allowed to change either of those.

SELECT
DB1T1.Name,
DB1T1.SomeIntValue,
DB2T2.Name,
DB2T2.SomeOtherIntValue
FROM [DB1].[dbo].[T1] AS DB1T1
LEFT JOIN [DB2].[dbo].[T2] AS DB2T2 ON
charindex(DB1T1.Name, DB2T2.Name) = 1

This is pretty much straight forward but we’ll get the following error if the tables have different collation.

DB-Collation-Error-Message.png

So, what should we do to overcome this problem?

The solution

We just need to make minor modification to the query and tell it to use the default database collation and we are good to go.

SELECT
DB1T1.Name,
DB1T1.SomeIntValue,
DB2T2.Name,
DB2T2.SomeOtherIntValue
FROM [DB1].[dbo].[T1] AS DB1T1
LEFT JOIN [DB2].[dbo].[T2] AS DB2T2 ON
charindex(DB1T1.Name COLLATE DATABASE_DEFAULT, DB2T2.Name COLLATE DATABASE_DEFAULT) = 1

EnJOIN your coding,

J.

 

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s