EastsideDev
EastsideDev

Reputation: 6639

CONCAT but return blanks for empty fields - MySQL

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?

Upvotes: 0

Views: 39

Answers (2)

ysth
ysth

Reputation: 98388

There are a couple of functions that really help with this, concat_ws and nullif.

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

fiddle

The nullif returns null for the component if it is either empty or null. Then concat_ws joins with spaces all its non-null arguments.

Upvotes: 1

Jon Christie
Jon Christie

Reputation: 54

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

Upvotes: 0

Related Questions