CONCAT Function - How to Concatenate multiple rows into single filed in MySQL

CONCAT Function - How to Concatenate multiple rows into single filed in MySQL


Concat function in MySQL is used to concatenate string values. Concat function really helps when you need to row values in table.

Syntax:


Concat('firstvalue','secondvalue',.....)


Example :
Let's say if we want to concat two values "aamir" and shahzad" and have space between them. we can use below example.

Select concat('aamir',' ','shahzad') as MyColumn;


How to use Concat function in MySQL or MariaDB
Let's create sample table customer and insert some rows and then we will use Concat to concatenate firstname and lastname column.

CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

-- insert sample data
insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza',null,39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');



Now let's use CONCAT function in MySQL to concatenate firstname and lastname.

Select *, concat(firstname,' ',ifnull(lastname,'')) as fullname from customer;


I have added ' ' between firstname and lastname, you can add any string in Concat function. I used the space so the full name is more readable.

Use Concat function to concatenate two columns in MySQL Table - MySQL developer Tutorial

Notice that the full name in first row is Null, that is because the lastname in first row is Null. If you will Concat Null with other values, the output is going to be Null. 

To handle Null values, you can use IFNull to replace null with blank space so you don't get the Null output, As shown below.


Select *, concat(IfNull(firstname,''),' ',IfNull(lastname,'')) 
as fullname from customer;


How to use IFNull in Concat function in MySQL to replace null with blank space - MySQL Tutorial

No comments:

Post a Comment