Use Collate in CONCAT
Solution 1
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
Solution 2
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
Solution 3
I fixed this problem by simply using the concat operator:
p.FULLNAMES + ' ' + p.SURNAME
Related videos on Youtube
![Carel](https://i.stack.imgur.com/sOcbe.jpg?s=256&g=1)
Carel
Currently working at a government organization's office productivity department
Updated on August 24, 2022Comments
-
Carel almost 2 years
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?
-
StuartLC about 10 yearsYou 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
-
t-clausen.dk about 10 years@StuartLC that could mess up your <order by>. It is better to change the space to the same collation as the other columns