Solution to the problem of NULL concat value in MySQL

Days in doing OpenCart development, the user's table needs to connect the phone number and area code, and then use 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 is obviously a value, so query the relevant concat method,

Explained as follows

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

Returns a string resulting from a connection parameter. If any of the arguments are 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 is null after execution, 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 time can be taken out of all the values, in addition to this method we can specify a value by default

The MySQL built-in ifull function can be used to give a default value to a Null value field at the time of the query, 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 result returned is an empty string because the null character is not NULL. If you need to replace the empty string or null value with Default-value, obviously ifnull is not possible, but use the case time statement can be done, the example is as follows:

code is as follows	copy code
Select C1, (case when c2 = ' "or C2 is null then ' Default-value ' else  C2 end) from test;