CODEWITHSUNDEEP
sql-serversql-server-2012concatenationcollate
Carel
Carel

Reputation: 2143

Use Collate in CONCAT

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?

Upvotes: 26

Views: 30953

Answers (3)

user764754
user764754

Reputation: 4236

I fixed this problem by simply using the concat operator:

p.FULLNAMES + ' ' + p.SURNAME

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44316

This will fix your problem:

SELECT CONCAT(p.FULLNAMES,' ' collate Latin1_General_CI_AS,p.SURNAME) AS NAME

The space is getting same default collation as the database, therefore it has to have same collation as your columns. Kind of silly in my opinion

Upvotes: 5

StuartLC
StuartLC

Reputation: 107237

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

Upvotes: 23

Related Questions