Solution to the problem where the CONCAT value in mysql is null

In mysql, The concat function has a feature that a value of null is returned no matter how much content the second character has. This feature makes us feel inconvenient in the instance application, but we need to use other methods to solve this problem.

In opencart development, we need to connect the user's phone number and area number in the User table, so we use the concat method,

The Code is as follows:	Copy code
Select concat (isdcode, telephone) FROM gb_customer

We found many NULL columns. telephone clearly had values, so we queried the related concat method,

The description is as follows:

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

 

Returns the string generated by the connection parameter. If any parameter is NULL, the return value is NULL. There may be one or more parameters,
The isdcode value in our table may be NULL, so the value after execution is NULL. Therefore, use IFNULL to determine whether isdcode is empty.

The Code is as follows:	Copy code
Select concat (IFNULL (isdcode, ''), telephone) FROM gb_customer

All values can be retrieved at this time. In addition to this method, we can specify a value by default.

Mysql built-in ifull function can be used to give a default value for the NULL Value Field During 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 database returns the default-value. Otherwise, the database returns its own value. However, when the value of the col1 field is a NULL string ("), the returned result is a NULL String because the NULL character is not NULL. If you want to replace an empty string or NULL value with default-value, ifnull is obviously not acceptable, but the case when statement can be used as an example:

The Code is as follows:	Copy code
Select c1, (Case when c2 = ''or c2 is null then 'default-value' else c2 end) From test;