How to merge two columns in sql server

Question

I want to merge two columns from deferent tables and when run my query shows this error:

Cannot resolve collation conflict for column 1 in SELECT statement

Here is my query

select (problems.name + department.dep_name) as m, usere.Use_name
    , emp_problems.op_date  
from usere, department, problems, emp_problems 
where usere.id = emp_problems.users_id and emp_problems.prob_id = problems.id   
and emp_problems.dup_dep_id = department.id
and emp_problems.emp_id = 37 and emp_problems.months = 11 and emp_problems.years =2020

Learn about sql collation: sqlshack.com/…

Gerardo Grignoli
– Gerardo Grignoli

2020-10-14 17:03:26 +00:00
Commented Oct 14, 2020 at 17:03 — Gerardo Grignoli
– Gerardo Grignoli, Commented Oct 14, 2020 at 17:03

Ilyes · Accepted Answer · 2020-10-14 17:14:53Z

You need to ALTER one of the columns to change the collation, or use COLLATE clause as

CREATE TABLE dbo.MyTable
(
  MyCol1 VARCHAR(20) COLLATE Latin1_General_100_CI_AI_SC,
  MyCol2 VARCHAR(20) COLLATE Latin1_General_100_CI_AI
);

Using ALTER:

ALTER TABLE dbo.MyTable 
ALTER COLUMN MyCol2 VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC;

Using COLLATE clause:

INSERT INTO dbo.MyTable VALUES
('Foo', 'Bar');
/* This will throw the error */
SELECT CONCAT(MyCol1, MyCol2) FROM dbo.MyTable;

/* This won't */
SELECT CONCAT(MyCol1, MyCol2 COLLATE Latin1_General_100_CI_AI_SC) FROM dbo.MyTable;

Take a look at Set or Change the Column Collation

Here is a db<>fiddle

Collectives™ on Stack Overflow

How to merge two columns in sql server

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related