Use Collate in CONCAT

sql-server sql-server-2012 concat collate
Carel picture Carel · May 15, 2014 · Viewed 22.3k times · Source

I was trying to concatonate 2 columns with a whitespace in between and got a collation error:

SELECT DISTINCT
    p.PERSON_ID,
    p.ID_NUMBER,
    CONCAT(p.FULLNAMES, CONCAT(' ', p.SURNAME)) AS NAME,
    o.ORG_NAME,
    w.WARD_DESCRIPTION AS WARD,
    ess.DESCRIPTION AS SECTOR

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the concat operation

The collation of both the offending columns in my database is: Latin1_General_CI_AS

So then I was trying to collate the whitespace to this collation, but I have no idea how to do this. My attempt:

CONCAT(p.FULLNAMES, (CONCAT((COLLATE Latin1_General_CI_AS = ' '), p.SURNAME))) AS NAME,

or something?

Answer

StuartLC picture StuartLC · May 15, 2014

You put the COLLATE after each field, viz in the worst case scenario:

SELECT DISTINCT
    CONCAT(p.FULLNAMES COLLATE Latin1_General_CI_AS, 
      (CONCAT(' ' COLLATE Latin1_General_CI_AS, 
          p.SURNAME COLLATE Latin1_General_CI_AS))) AS NAME
FROM Person p

Related questions

How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More
Auto increment primary key in SQL Server Management Studio 2012

How do I auto increment the primary key in a SQL Server database table, I've had a look through the forum but can't see how. I've looked the the properties but can't see an option, I have seen an answer …

Read More
Convert Date format into DD/MMM/YYYY format in SQL Server

I have a query in SQL, I have to get a date in a format of dd/mm/yy Example: 25/jun/2013. How can I convert it for SQL server?

Read More