Skip to main content

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


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 )

Popular posts from this blog

How to apply for a new ration card and what are the documents required?

List of documents required for new ration card application in Kerala and how to apply


Application for a new ration card should be addressed to Taluk Supply Officer (TSO) / City Rationing Officer (CRO) of applicant's residing area.

Primary document required are Residence certificate, Income certificate and incase the applicant's name is in another ration card then he/she should submit the reduction certificate ontained from previous TSO / CRO as proof for removing his/her name from old Ration card.

You can get the new application from your currently residing Taluk Supply Office.

Payback Points - How to redeem - How to merge multiple payback accounts - Block Payback card - Payback customer care

Joomla and Forum Integration - Integrating Forums to Joomla

Joomla is one of the most popular CMS opensource packages. It is very easier to develop website's using Joomla. You just need to download Joomla package from Joomla's Official website www.joomla.org  and install it on your domain and later adding customizations to templates and feature and Your website is ready :). Now a days most websites provides a forum section for it users for discussing various article topics, gathering opinions etc.



Following are some best know forum opensource packages which can be integrated with Joomla and create a new forum experience for users


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 »
Member
Search This Blog