Menu
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?
838
COLLATE can be used at the database level or the column level. Since you are trying to UNION two tables, using the column collation on the needed columns will resolve your query. Of course, if you have several columns with conflicting collations you will need to define their collations as well.
You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify a collation when you create a table using SQL Server Management Studio. If you do not specify a collation, the column is assigned the default collation of the database.
A collation specifies the bit patterns that represent each character in a dataset. Collations also determine the rules that sort and compare data. SQL Server supports storing objects that have different collations in a single database.
Collations in SQL Server provide sorting rules, case, and accent sensitivity properties to data. A collation defines bit patterns that represent each character in metadata of database. SQL Server supports storing objects that have different collations in database.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
