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?

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)