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';
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.
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';
NULL middle name; i.e. "Bobby . Tables". It does address the immediate issue, however.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.
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';
