Home  »     »     »     »  MySQL CONCAT() function returns NULL when one of the argument field value is NULL - Fix

MySQL CONCAT() function returns NULL when one of the argument field value is NULL - Fix

Thursday, October 23, 2014


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 )



How to link to this page?
If you wish to link to this page from your website, simply Copy and paste the above HTML code to your web page. It will appear on your page as:
MySQL CONCAT() function returns NULL when one of the argument field value is NULL - Fix.




Share this!




comments powered by Disqus

This Weeks 7 Popular Posts


Subscribe to Recent Posts by Email
Stay connected to CROZOOM with regular Email notices of new Techie articles and IT Jobs. Updates will be delivered to your Inbox as soon as they are posted online.

Enter Your Email Address:  

Delivered by FeedBurner   RSS Feed

Search this Blog   



Urgent Openings for PHP trainees, Andriod / IOS developers and PHP developers in Kochi Trivandrum Calicut and Bangalore. Please Send Your updated resumes to recruit.vo@gmail.com   Read more »