Question 21668 in Mysql Question posted in
The official MySQL documentation can be found here.

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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search