Concatenate multiple columns in SQL Server

We could use CONCAT function or + (plus sign) to concatenate multiple columns in SQL Server. Both CONCAT and (+) result if both operands have values different from NULL. When we use CONCAT function the result defaults to a none NULL value while NULL is concatenated with out text whereas when we use (+) the resulting output will default to NULL.
Let us see this example to make the above statement more sense.

SELECT FirstName,
       MiddleName,
       FirstName + MiddleName AS [FirstName + MiddleName],
	CONCAT(FirstName, MiddleName) AS [CONCAT(FirstName, MiddleName)]
FROM Person.Person;

The other difference is (+) require operands to be a string value or need to be converted to string where as CONCATE accept other non-string data types such as INT and DATE.

SELECT FirstName,
       Firstname + 123 AS [FirstName + 123]
FROM Person.Person;

SELECT FirstName,       
	CONCAT(Firstname , 123) AS [FirstName + 123]
FROM Person.Person;

SELECT FirstName,
       Firstname + GETDATE() AS [FirstName + GETDATE()]
FROM Person.Person;

SELECT FirstName,       
	   CONCAT(Firstname , GETDATE()) AS [FirstName + GETDATE()]
FROM Person.Person;

There is no one function like GROUP_CONCAT() in MySQL that we could use to concat multiple row values into a single column value in SQL Server but we could use other methods like for example if we want to concat all IDs that has has similar FirstName we could do it as

DECLARE @Separator AS NVARCHAR = N'-'; 
 
SELECT DISTINCT FirstName, 
                STUFF((SELECT @Separator 
                              + CAST(C.BusinessEntityID AS NVARCHAR) 
                       FROM   person.Person C 
                       WHERE  P.FirstName = C.FirstName 
                       FOR xml path('')), 1, 1, '') AS IDList 
FROM   person.Person P;

Comments

Gerald says

January 4, 2014 at 6:12 pm

THANK you, your response cleared the fog from my mind. I was able to combine 3 integer fields (10, 99, 125) into a single text value with a value of (1099125). All other solution resulted in the sum of the 3 integers. Exact code used was: concat(col1,col2,col3) as col4. Again thanks.
Gerald says

January 4, 2014 at 9:59 pm

More fiddling discloses the concat(col1,col2,col3) as col4 does not work on creating a table, only works on views. I am using mysql 5.5
- Biz Nigatu says
  
  January 5, 2014 at 1:39 am
  
  Gerald,
  As far as I know MySQL does not support computed column like SQL Server. I don’t know how you feel about using triggers but you could add Before Insert and Before Update triggers to your table to have your fourth column calculated dynamically. Check the sample triggers I wrote for you below
  
  create table test.table1(
  col1 varchar(10),
  col2 varchar(10),
  col3 varchar(10),
  col4 varchar(10)
  );
  
  DELIMITER $$
  
  create trigger test.trg_table1_inst
  before insert on test.table1 for each row
  /*referencing new as new old as old*/
  begin
  set new.col4=concat(new.col1, new.col2, new.col3);
  end$$
  
  DELIMITER ;
  /* check insert
  insert into test.table1(col1,col2,col3) values(1,2,3);
  select * from test.table1;
  */
  
  DELIMITER $$
  
  create trigger test.trg_table1_updt
  before update on test.table1 for each row
  begin
  set new.col4=concat(new.col1, new.col2, new.col3);
  end$$
  
  DELIMITER ;
  
  /* check update
  update test.table1 set col3 =4;
  select * from test.table1;
  */
  - Gerald says
    
    January 5, 2014 at 11:06 pm
    
    Thanks, I am sure you spent a lot of time coming up with your response and I am grateful. But the database I am creating has a lot of tables that rely upon joining columns 1, 2 & 3 as col4. Setting up a program, like what you are proposing will take too much time. The methodology I have proposed seems to work, but I won’t know until I load data into the tables. Will see and if it doesn’t work I will take another crack at it using your methodology. Again thanks