Data

T-SQL – Collate Columns on Joins

John

This is an extension of my Cannot Resolve Collation Conflict post. However, in this post, I’ll show how I resolved a collation conflict without changing the underlying metadata and doing it on the fly within the joins.

Creating Tables

In the code below, we have tblCustomer with FirstName and LastName collated as SQL_Latin1_General_CP1_CI_AS. The tblPurchases table has those columns collated as Latin1_General_CI_AS.

CREATE TABLE [dbo].[tblCustomer]
(
    [CustomerID] int         NOT NULL IDENTITY
  , [FirstName]  varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
  , [LastName]   varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
);

INSERT INTO [dbo].[tblCustomer]
    (
        [FirstName]
      , [LastName]
    )
VALUES
     ('John'    -- FirstName - varchar(20)
    , 'Xiong'   -- LastName - varchar(20)
    )
   , ('Elvis'   -- FirstName - varchar(20)
    , 'Presley' -- LastName - varchar(20)
   );

CREATE TABLE [dbo].[tblPurchases]
(
    [PurchaseID]      int         NOT NULL IDENTITY
  , [FirstName]       varchar(20) COLLATE Latin1_General_CI_AS NULL
  , [LastName]        varchar(20) COLLATE Latin1_General_CI_AS NULL
  , [ItemDescription] varchar(20) NULL
);

INSERT INTO [dbo].[tblPurchases]
    (
        [FirstName]
      , [LastName]
      , [ItemDescription]
    )
VALUES
     ('John'    -- FirstName - varchar(20)
    , 'Xiong'   -- LastName - varchar(20)
    , 'Coffee'  -- ItemDescription - varchar(20)
    )
   , ('Elvis'   -- FirstName - varchar(20)
    , 'Presley' -- LastName - varchar(20)
    , 'Donut'   -- ItemDescription - varchar(20)
   );

The Error

When trying to join the two tables based on names, we’ll get a collation conflict error message like so:

SELECT          *
  FROM          [dbo].[tblCustomer] [C]
  INNER JOIN    [dbo].[tblPurchases] [P] ON
                [P].[FirstName] = [C].[FirstName]
                AND [P].[LastName] = [C].[LastName];

Msg 468, Level 16, State 9, Line 46
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

The Solution

To quickly resolve this, we can collate one set of the columns that are used in the join. In this example, I decided to change the collation of columns of tblCustomer to match the Latin1_General_CI_AS collation of tblPurchases columns:

SELECT          *
  FROM          [dbo].[tblCustomer] [C]
  INNER JOIN    [dbo].[tblPurchases] [P] ON
                [P].[FirstName] = [C].[FirstName] COLLATE Latin1_General_CI_AS

                AND [P].[LastName] = [C].[LastName] COLLATE Latin1_General_CI_AS;

Another Solution

I happened to find somewhere (I’ll need to link it once I find it, again) showing how they used a temp table if you happened to be moving data through staging areas:

CREATE TABLE [#tblCustomerLatin1Collate]
(
    [CustomerID] int         NOT NULL
  , [FirstName]  varchar(20) COLLATE Latin1_General_CI_AS NULL
  , [LastName]   varchar(20) COLLATE Latin1_General_CI_AS NULL
);

INSERT INTO [#tblCustomerLatin1Collate]
    (
        [CustomerID]
      , [FirstName]
      , [LastName]
    )
SELECT  [CustomerID]
      , [FirstName]
      , [LastName]
  FROM  [dbo].[tblCustomer];

SELECT          *
  FROM          [#tblCustomerLatin1Collate] [C]
  INNER JOIN    [dbo].[tblPurchases] [P] ON
                [P].[FirstName] = [C].[FirstName]
                AND [P].[LastName] = [C].[LastName];

Happy Coding!

Back To Top