Solution to the problem of concat value NULL in MySQL

One of the features of the concat function in MySQL is that there is a value of NULL so no matter how much of the second character is returned as empty, this feature makes it possible for us to find it inconvenient in an instance application, but the implementation is so we need to use other solutions.

Days in doing OpenCart development, need to user table in the user's telephone number and area code connection, so used the Concat method,

The code is as follows	Copy Code
SELECT CONCAT (isdcode,telephone) from Gb_customer

Unexpectedly found a lot of NULL columns, telephone obviously have value, so query the relevant concat method,

Explained below

The code is as follows	Copy Code
MySQL CONCAT (str1,str2,...)

Returns the string that results from the connection parameter. If any one of the arguments is NULL, the return value is null. There may be one or more parameters,
The value of isdcode in my table may be null, so the value after execution is empty, so use a ifnull to determine if Isdcode is empty

The code is as follows	Copy Code
SELECT CONCAT (Ifnull (Isdcode, "), telephone) from Gb_customer

This is the time to be able to take out all the values, in addition to this method we can specify a value by default

MySQL's built-in ifull function can be used to give a default value to a Null value field at query time, for example:

The code is as follows	Copy Code
Select Ifnull (col1, ' Default-value '), col2 from test;

When the col1 field of the test table is NULL, the result returned by the database is Default-value, otherwise the value of itself is returned. However, when the value of the Col1 field is an empty string ("), the returned result is an empty string because the null character is not NULL. If you need to replace an empty string or null value with Default-value, it is obvious that ifnull is not possible, but using the case when statement can be done as follows:

The code is as follows	Copy Code
Select C1, (Case when c2 = "or C2 is null then ' default-value ' else C2 end) From Test;

Source:

Solution to the problem of concat value NULL in MySQL