How can we overcome the property of CONCAT() function that it returns NULL if any one of the arguments is NULL, especially when we want to concatenate the values in columns and any column's value is NULL?

The above properties are of little use, especially when we want to concatenate values in columns and the value of any column is NULL. To overcome this problem we can use IFNULL() function and CONCAT() function. To understand it, let's consider the example from the table "Student_name;" There is the following data in it -

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, the output is as follows -

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

However, we know that this is not a useful output, Because the property of the CONCAT() function is that if any parameter is NULL, it returns NULL. We can overcome this property with the help of IFNULL() function as shown 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)

The above is the detailed content of How can we overcome the property of CONCAT() function that it returns NULL if any one of the arguments is NULL, especially when we want to concatenate the values in columns and any column's value is NULL?. For more information, please follow other related articles on the PHP Chinese website!