CONCAT names with condition

Jassim Rahma

Hi, I have first_name, middle_name and last_name in my customers table. I want to do

SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name)

The middle name is optional so I want to ignore it if it's blank to be:

SELECT CONCAT(first_name, ' ', last_name)

because if I do it the normal way I'll have extra space between the first_name and last_name

Mycroft Holmes

You can use ISNULL on the first middle name or a case statement. Case statement is more robust as it caters for ''.

Never underestimate the power of human stupidity RAH

PIEBALDconsult

As long as you're doing it that way, you might as well use REPLACE. :-D

You'll never get very far if all you do is follow instructions.

GuyThiebaut

Without using CONCAT you can do this:

rtrim(isnull(first_name,'',first_name+' ') + isnull(middle_name,'',middle_name+' ') + isnull(last_name,'',last_name))

The rtrim will remove a trailing space where the person does not have a last name.

― Christopher Hitchens