SQL - Concat full name, and a space only if last name is present

Question

I am currently concatenating a first and a last name like this (using PostgreSQL):

concat(customers.firstname, ' ', customers.lastname)

The problem I am having here is that I have customers that were entered with just a first name (ie. "Ronaldo"). The query above will return "Ronaldo " and that will not match.

I can use my server-side language to check for trailing spaces, but if there is a customer somewhere in my database that was accidentally typed in with a trailing space (ie. "Harry Potter "), then I won't match that one either.

I am looking for SQL that will return "Ronaldo", "John Smith", and "Harry Potter ". So basically I want to include a space between first and last name if last name is not equal to "" (empty string). If lastname is "" (empty string) then I want no space following the firstname. Is this possible with just SQL? Or will I have to figure out how to parse this appropriately with a server-side language?

Trilogy

can you not use a case statement instead? (I don't know the rest of your query): case when customers.lastname is null then customers.firstname else concat(customers.firstname, ' ', customers.lastname) end – Hollander

This is exactly what I needed. Thank you! Found the documentation too: postgresql.org/docs/7.4/static/functions-conditional.html. – Trilogy

You're welcome; You should post this as the accepted answer -- make a new answer and accept it. – Hollander

@Hollander Simpler a bit: concat(customers.firstname, ' ' || nullif(customers.lastname, '')) – Steinway

fair enough -- just be warned that this is postgre-specific syntax. I know the question is for postgre, but I personally try to avoid engine-specific syntax like the plague. – Hollander

Answer 1

You could use a TRIM() function call to strip off any leading or trailing spaces :

TRIM(CONCAT(customers.firstname, ' ', customers.lastname))

TRIM() also allows you to specific the characters you which to remove along with the location within the string (i.e. beginning, end, both, etc.) using the following syntax :

TRIM([LEADING | TRAILING | BOTH] [characters] FROM YourColumnOrString)

Bithynia

Wouldn't this trim the trailing space from "Harry Potter "? – Trilogy

Yes, it would, which should accomplish the same thing if you just had "Harry", as your CONCAT() would cause it to be "Harry " and then the trim would take care of the rest. – Bithynia

Answer 2

I use this:

trim(both from COALESCE(firstname, '') || ' ' || COALESCE(lastname, '')

if both first and last name can be null.

Infantile

Answer 3

This worked for me:

case
  when customers.lastname = ''
    then customers.firstname
  else
    concat(customers.firstname, ' ', customers.lastname)
end

Documentation for case: https://www.postgresql.org/docs/7.4/static/functions-conditional.html.

Special thanks to daf.

Trilogy

Answer 4

Try this... This will remove all (start & end) the whitespaces

SELECT RTRIM(CONCAT(LTRIM(RTRIM(First_Name)) , ' ' , LTRIM(RTRIM(Last_Name)))) 
AS 'Full_Name'
FROM tableName;

If you also want to insert middle name, you can try something like this...

SELECT (RTRIM(LTRIM(RTRIM(First_Name)) + ' ' + LTRIM(RTRIM(Middle_Name))) + ' ' + LTRIM(RTRIM(Last_Name))) 
AS 'Full_Name'
FROM tableName;

Juan

Answer 5

You can use the concat_ws string function to concatenate strings (ignoring NULL values), where the first argument is the delimiter and the rest are to be concatenated strings. See docs
Example:

SELECT
    concat_ws(' ', customers.firstname, NULLIF(customers.lastname, '')) AS fullname
FROM customers

This solution should give the following results (1-3 as per your requirements):

1. 'John' and 'Smith' => 'John Smith'
2. 'Ronaldo' and '' => 'Ronaldo'
3. 'Harry' and 'Potter ' => 'Harry Potter '
4. 'Ronaldo' and NULL => 'Ronaldo'

Kirghiz