Sobmac Sobmac -3 years ago 179
SQL Question

Concatenation error - collation conflict between concat operation

I'm running a query but it's giving an error.

Query

Select [Material],CONCAT(case when len([PO Item]) = 3 then '00' when len([PO Item]) = 2 then '000' else '0000' end,[PO Item]) from Vendortable


Error


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


What I want to do it append 0's at the front of the number to make it a 5 digit number.

What is this error? how to solve it?

Answer Source

Collation refers to the rules that govern how SQL compares and sorts strings, for example when sorting rows by a string is αΊ₯ treated the same as a, or is it sorted separately? Collation determines that, among many other things. When comparing two columns, their collation must match so SQL Server knows which one to use.

These rules are usually the same within a database (the "default collation"), but often if you have merged databases, upgraded a legacy database and added to it, or someone has changed default collations on a column then two columns in the same database could have different collations. As @MtwStark has pointed out, you can force collation for a particular comparison, and if absolutely necessary, you can change the collation on one column to match another.

It appears to me that your database default collation is SQL_Latin1_General_CP1_CI_AS, and your column collation on the [PO Item] column is Latin1_General_CS_AS_KS_WS, hence the error.

I also agree that using the right() function is better for padding to the left:

select right('00000' collate SQL_Latin1_General_CP1_CI_AS
         + [PO Item] collate SQL_Latin1_General_CP1_CI_AS, 5) 
from Vendortable

A word of warning though - don't alter database or column collation unless you know what you are doing, or you could create a world of trouble (been there). If this is a persistent issue with your database, get a good DBA to look at it and advise a solution.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download