I'm running a query but it's giving an error.
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
Cannot resolve the collation conflict between
"Latin1_General_CS_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the
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.