CODEWITHSUNDEEP
mysqlsqldatabasenull
Seehyung Lee
Seehyung Lee

Reputation: 610

Getting empty result if there is 'null' value in CONCAT

I expected to get full name without middle initial if there is no middle name, but if column "M_NAME" is Null, the select statement returns empty. How can i solve this?

   SELECT CONCAT(`Employee`.`F_NAME`, ' ', LEFT(`Employee`.`M_NAME`, 1), '. ', `Employee`.`L_NAME`) FROM `ccms`.`Employee` WHERE HR_ID = '223';

Upvotes: 0

Views: 1229

Answers (4)

user1843951
user1843951

Reputation: 41

another simple solution is use CONCAT_WS. It will work defenitely.

SELECT CONCAT_WS('' F_NAME, '', LEFT(M_NAME, 1), L_NAME) FROM ccms.Employee WHERE HR_ID = '223';

Upvotes: -1

D Mac
D Mac

Reputation: 3809

Use IFNULL().

Any place you have a possibly NULL field, wrap it with IFNULL(whatever, '') and then you'll get an empty string instead of a result-killing NULL.

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191749

CONCAT returns NULL if any argument is null. You can solve this by making sure that no null argument is null by wrapping any nullable column in either IFNULL or COALESCE (the latter can take more than two arguments).

SELECT
    CONCAT(
        IFNULL(F_NAME, ''),
        ' ',
        IFNULL(CONCAT(LEFT(M_NAME, 1), '. '), ''),
        IFNULL(L_NAME, '')
    )
FROM
    ccms.Employee
WHERE
    HR_ID = '223';

What this does is replace NULL column values with an empty string, which is probably your intent. Note that I updated the selection of M_NAME so that the period is only added if the value is not null by using this very behavior.

EDIT: You can use backticks and qualify column names if you want, but it's not necessary for this exact query.

Upvotes: 2

John Conde
John Conde

Reputation: 219804

Use COALESCE()

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

SELECT CONCAT(`Employee`.`F_NAME`, ' ', COALESCE(LEFT(`Employee`.`M_NAME`, 1), ''), '. ', `Employee`.`L_NAME`) FROM `ccms`.`Employee` WHERE HR_ID = '223';

Upvotes: 2

Related Questions