CONCAT but return blanks for empty fields

MySQL 8

What I would like to do, is return full_name from a table, with first_name, middle_name, last_name, but would like a single space in between them, and no space if any of the values are either null or an empty string.

I tried:

SELECT id, CONCAT(
    IF(LENGTH(first_name),first_name,' '), 
    IF(LENGTH(middle_name),middle_name,' '), 
    IF(LENGTH(last_name),last_name,'')
    ) as name from users

However, this works ONLY if the middle_name is either empty or null. If the middle_name has a value, then it splices them all together, with no spaces.

So, John Doe, returns John Doe, and John F Doe, returns JohnFDoe.

I am using LENGTH as I believe it works for both NULL and empty string.

Any ideas?

CodePudding user response：

Give this a shot:

SELECT id, CONCAT(
    IF(LENGTH(first_name),first_name,' '), 
    IF(LENGTH(middle_name),middle_name   ' ',' '), 
    IF(LENGTH(last_name),last_name,'')
    ) as name from users

If that doesn't work, you could try to set this up outside and above the given block:

CASE WHEN middle_name = middle_name THEN middle_name   ' ' ELSE '' END

CodePudding user response：

There are a couple of functions that really help with this:

concat_ws(' ',nullif(first_name,''),nullif(middle_name,''),nullif(last_name,''))