Concatenation strings get from columns of table in SQL Server 2012
How join strings comming from columns of table as one result string?
It is several ways for it.
Begin with create database with this example table. And set data into this table with NULL values in some cells.
USE mydb; GO SELECT id ,name1 ,name2 FROM mydb.dbo.people ;
First You use add operator between strings get them from diffrent columns.
USE mydb; GO SELECT ( name1 + ' ' + name2 ) as name FROM mydb.dbo.people ;
You get this message:Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "Polish_CS_AS" and "Polish_CI_AS" in add operator.
Why? Column name1
has Polish_CI_AS
collation, but name2
column has Polish_CS_AS
collation. So if You would like to join them You must change one of them for the same as another. Then it will be work. So change collation of name1
column to Polish_CS_AS
– collation the same as in name2
column.
ALTER TABLE people ALTER COLUMN name1 varchar(100) COLLATE Polish_CS_AS;
Execute it and select statement:
USE mydb; GO SELECT ( name1 + ' ' + name2 ) as name FROM mydb.dbo.people ;
You see as result this records:
As You may notice if one value from columns which data are joined is NULL, the result always is NULL ( example: name1=NULL and name2=miller give NULL ) .
How unique this situation?
You may use concat
function.
Syntax:
CONCAT(string1, string2, ... );
In this way You join values from name1 and name2 columns.
USE mydb; GO SELECT CONCAT( name1 , ' ' , name2 ) as name FROM mydb.dbo.people ;
You see message:Cannot resolve the collation conflict between "Polish_CS_AS" and "Polish_CI_AI" in the concat operation.
How resolve this problem? The columns are the same collation(Polish_CS_AS), but empty string has Polish_CI_AI collation(inherit from database), so You may change collation it for this string in this query.
USE mydb; GO SELECT CONCAT( name1, ' ' COLLATE Polish_CS_AS , name2) FROM mydb.dbo.people ;
And it is result:
Notice that if one of column contains NULL
value this value is treat as empty string and values from another columns is joined. Last row has NULL
values in name1
and name2
columns so You see empty string.
Another way resolve problem with collation of this empty string is change collation database.
USE master; GO ALTER DATABASE mydb COLLATE Polish_CS_AS; USE mydb; GO SELECT CONCAT( name1, ' ' , name2) as name FROM mydb.dbo.people ;