MySQL String Concatenate Functions - MySQL CONCAT() function returns NULL when one of the argument field value is NULL
In MySQL for concatenating two or more stings or Field values we use CONCAT().It returns the string that results from concatenating the arguments. So even if all the argument fields except one have valid data it will return NULL
Syntax: CONCAT('STRING1','STRING2','STRING3',....)
Example Query: SELECT CONCAT(APPLICANT1, ', ' , APPLICANT2) AS APPLICANTS FROM TBL_APPLICATION;
There is an issue with this function. ie, CONCAT() returns NULL if any of the arguments is NULL.
mysql> SELECT CONCAT('First Name', NULL, 'Last Name');
>> NULL
Suppose you are fetching a Concatenation string of Applicant names from a MySQL Table
SELECT CONCAT(APPLICANT1, ', ' , APPLICANT2) AS APPLICANTS FROM TBL_APPLICATION;
In this case if APPLICANT2 field is NULL then it will return NULL
You can do a work around on CONCAT() using the IFNULL() to fix this
SELECT CONCAT(IFNULL(APPLICANT1,''),' ',IFNULL(APPLICANT2,'')) AS APPLICANTS
The IFNULL function will check whether the field value is empty , and returns a blank value (which is not NULL)
OTHER OPTION is to use MySQL Concatenate With Separator (CONCAT_WS) Function
CONCAT_WS - Concatenate With Separator
Syntax: CONCAT_WS('SEPARATOR','STRING1','STRING2','STRING3',....)
Example Query: SELECT CONCAT_WS(', ',APPLICANT1,'APPLICANT2') AS APPLICANTS FROM TBL_APPLICATION;
Function skip any NULL values given after the separator string. Example given below:
mysql> SELECT CONCAT_WS(',','Name',NULL,'Family Name');
>> 'Name,Family Name'
References:
MySQL Tutorial "CONCAT_WS()"
MySQL Tutorial "CONCAT()"
MySQl Control Flow Functions ( IFNULL )
