Use Collate in CONCAT

Question

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?

score 23 · Accepted Answer · answered May 15 '14 at 08:42

23

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

answered May 15 '14 at 08:42

StuartLC

104,537
17
209
285

2

You can probably avoid collating the literal by collating via DATABASE_DEFAULT, viz `SELECT DISTINCT CONCAT(p.FULLNAMES COLLATE DATABASE_DEFAULT, (CONCAT(' ', p.SURNAME COLLATE DATABASE_DEFAULT))) AS NAME FROM Person p` – StuartLC May 15 '14 at 08:50
1

@StuartLC that could mess up your . It is better to change the space to the same collation as the other columns – t-clausen.dk May 15 '14 at 08:56

score 5 · Answer 2 · answered May 15 '14 at 08:47

5

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

answered May 15 '14 at 08:47

t-clausen.dk

43,517
12
59
92

score 2 · Answer 3 · answered May 13 '15 at 12:02

2

I fixed this problem by simply using the concat operator:

p.FULLNAMES + ' ' + p.SURNAME

answered May 13 '15 at 12:02

user764754

3,865
2
39
55

Use Collate in CONCAT

3 Answers3

Linked