How can we overcome the property of CONCAT() function that it returns NULL if any one of the argument is NULL, especially when we want to concatenate the values from the column and any of the columns have NULL as its value?

The above-said property is not useful especially in the case when we want to concatenate the values from the column and any of the columns have NULL as its value. To overcome this, we can use IFNULL() function along with CONCAT() function. To understand it, we consider the example from the table ‘Student_name; which have the following data −

mysql> Select * from Student_Name;
+---------+-------+---------+
| FName   | Mname | Lname   |
+---------+-------+---------+
| Rahul   | NULL  | Singh   |
| Gaurav  | Kumar | NULL    |
| Harshit | NULL  | Khurana |
| Yash    | Pal   | Sharma  |
+---------+-------+---------+
4 rows in set (0.00 sec)

Now, suppose if we want to concatenate the values of Fname, Mname and Lname columns then following would be the output −

mysql> Select CONCAT(Fname,Mname,Lname)AS Name from Student_Name;
+---------------+
| Name          |
+---------------+
| NULL          |
| NULL          |
| NULL          |
| YashPalSharma |
+---------------+
4 rows in set (0.00 sec)

But, we know that this is not useful output due to the property of CONCAT() function that it returns NULL if any of the argument is NULL. We can overcome this property with the help of IFNULL() function as did in the following query −

mysql> Select CONCAT(IFNULL(Fname,''),IFNULL(Mname,''),IFNULL(Lname,''))AS Name from Student_Name;
+----------------+
| Name           |
+----------------+
| RahulSingh     |
| GauravKumar    |
| HarshitKhurana |
| YashPalSharma  |
+----------------+
4 rows in set (0.06 sec)

Samual Sam

Learning faster. Every day.

Updated on: 22-Jun-2020

219 Views

Kickstart Your Career

Get certified by completing the course

Get Started