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

Question

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.

`middle + ' '` is a mathematical expression. You probably mean `CONCAT(middle, ' ')`. — kmoser, Jan 26 '22 at 19:33
Does this answer your question? [How to check if field is null or empty in MySQL?](https://stackoverflow.com/questions/17832906/how-to-check-if-field-is-null-or-empty-in-mysql) — WOUNDEDStevenJones, Jan 26 '22 at 19:39

Bill Karwin · Accepted Answer · 2022-01-27T13:48:25.043

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.

nice use of nullif. but it's needed for first and last too – ysth Jan 27 '22 at 00:07 — ysth, Jan 27 '22 at 00:07

kmoser · Answer 2 · 2022-01-26T19:42:33.220

0

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.

edited Jan 26 '22 at 19:42

answered Jan 26 '22 at 19:38

kmoser

8,780
3
24
40

this gets rid of double (or more) spaces inside of the name parts, which may or may not be a good thing – ysth Jan 26 '22 at 19:40

score 0 · Answer 3 · answered Jan 26 '22 at 19:39

0

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)
    )
)

answered Jan 26 '22 at 19:39

ysth

96,171
6
121
214

what happens if it is blank then in this sql code? – userwithquestions Jan 26 '22 at 19:41
if it is blank it is doesn't get added (for middle and first) or gets added but to no effect (for last) – ysth Jan 26 '22 at 19:42

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

3 Answers3