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

Updated on 22-Jun-2020 07:39:12

• Related Questions & Answers
• What is the advantage of CONCAT_WS() function over CONCAT() function when we want to concatenate the values from the column and any of the columns have NULL as its value?
• What MySQL EXPORT_SET() function returns if any of the argument is NULL?
• What MySQL returns if we pass column name, containing a NULL value, as one of the arguments of CONCAT() function?
• What MySQL returns if the argument of QUOTE() function is NULL?
• What MySQL returns if we use NULL, as both the arguments, as one of the argument and as a separator, in CONCAT_WS() function?
• What MySQL returns if the first argument of INTERVAL() function is NULL?
• Concatenate two columns when one of such column values is null in MySQL
• When MySQL LOCATE() function returns NULL as the output?
• What would be the result if we perform any kind of arithmetic calculations in MySQL having one of the arguments as NULL?
• What MySQL returns if the list of strings, provided as argument in FIELD() function, are NULL?
• What MySQL returns when we use DISTINCT clause with the column having multiple NULL values?
• What MySQL MAKE_SET() function returns if the value of the bit is 1 and the first string is NULL?
• What MySQL COALESCE() function returns if it has a blank, but not NULL, as the first argument?
• What we can conclude if the final value in the NULL indicator is -2?
• How MySQL evaluates if we use EXISTS operator with the subquery that returns NULL?