CODEWITHSUNDEEP
mysqlsql
userwithquestions
userwithquestions

Reputation: 35

MySQL how to concatenate middle, first, and last name while handling empty?

Let's say we have a MySQL table like this:

middle   first    last       name
---------------------------  -------------
         reyes    sanchez    reyes sanchez
         antonio  cruz       antonio cruz
m        michael  middleton  m michael middleton
a        greg     allen      a greg allen

How can I write a statement to combine these 3 columns where it looks like the column called name? We can assume that middle, first, and last columns are not nullable but can be empty, have empty spaces, or have spaces on the left/right side if nonempty?

I tried to write something like this:

CONCAT(middle + ' ', RTRIM(first), RTRIM(last)) AS name

But even the first result showed this: 0reyessanchez and I am not sure how to insert a space between and I have no idea why MySQL is inserting a 0 instead of taking the blank.

Upvotes: 1

Views: 758

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562270

mysql> select middle, first, last from mytable;
+--------+---------+-----------+
| middle | first   | last      |
+--------+---------+-----------+
|        | reyes   | sanchez   |
|        | antonio | cruz      |
| m      | michael | middleton |
| a      | greg    | allen     |
+--------+---------+-----------+

mysql> select concat_ws(' ', 
     nullif(trim(middle), ''), 
     nullif(trim(first), ''), 
     nullif(trim(last), '')) 
    as fullname 
  from mytable;
+---------------------+
| s                   |
+---------------------+
| reyes sanchez       |
| antonio cruz        |
| m michael middleton |
| a greg allen        |
+---------------------+

MySQL's CONCAT_WS() function ignores NULLs.

Upvotes: 1

kmoser
kmoser

Reputation: 9273

This will give you the value you want:

TRIM( REGEXP_REPLACE( CONCAT(middle, ' ', first, ' ', last), '[[:space:]]+', ' ') )

Explanation:

CONCAT(middle, ' ', first, ' ', last) concatenates the three strings.

PREG_REPLACE() replaces multiple spaces with a single space (see https://stackoverflow.com/a/52855455/378779).

And finally TRIM() trims any leading any trailing spaces.

Upvotes: 0

ysth
ysth

Reputation: 98388

You have to explicitly check if a column is non-blank before adding a space after it:

rtrim(
    concat(
        if(length(trim(middle)),concat(trim(middle),' '),''),
        if(length(trim(first)),concat(trim(first),' '),''),
        ltrim(last)
    )
)

Upvotes: 0

Related Questions