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; |
Read more
http://technet.microsoft.com/en-us/library/hh231515.aspx
http://technet.microsoft.com/en-us/library/ms177561.aspx
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.
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
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;
*/
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