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
– kmoser, Commented Jan 26, 2022 at 19:33
Does this answer your question? How to check if field is null or empty in MySQL? — WOUNDEDStevenJones
– WOUNDEDStevenJones, Commented Jan 26, 2022 at 19:39

Bill Karwin · Accepted Answer · 2022-01-27 13:48:25Z

1

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.

edited Jan 27, 2022 at 13:48

answered Jan 26, 2022 at 19:44

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ysth Over a year ago

nice use of nullif. but it's needed for first and last too

ysth · Accepted Answer · 2022-01-26 19:39:41Z

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, 2022 at 19:39

ysth

99.1k6 gold badges126 silver badges220 bronze badges

2 Comments

userwithquestions Over a year ago

what happens if it is blank then in this sql code?

ysth Over a year ago

if it is blank it is doesn't get added (for middle and first) or gets added but to no effect (for last)

kmoser · Accepted Answer · 2022-01-26 19:42:33Z

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, 2022 at 19:42

answered Jan 26, 2022 at 19:38

kmoser

9,3693 gold badges28 silver badges45 bronze badges

1 Comment

ysth Over a year ago

this gets rid of double (or more) spaces inside of the name parts, which may or may not be a good thing

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related