SQL: Concat operator and Spaces

So I had a pressing need to query my database and concatenate several values from a row together (using either concat or ||), but with spaces in between.

But I don't want two spaces if the value in between is empty.

It is surprisingly difficult to do in SQL, but there are some options available.

First the data:

TITLE	FIRSTNAME	MIDDLENAME	FAMILYNAME
	Ichabod		Crane
Dr.	Thomas		Lancaster
	Philip	Martin	Brown

0. No solution

select TITLE || ' ' || FIRSTNAME || ' ' || MIDDLENAME || ' ' || FAMILYNAME from PERSON;

So, if you use the SQL statement above, you get the problem, extraneous spaces. For example "[ ]Ichabod[ ][ ]Crane". We wish to eliminate those.

1. COALESCE

select coalesce(TITLE || ' ','') || coalesce(FIRSTNAME || ' ','') || coalesce(MIDDLENAME || ' ','') || FAMILYNAME from PERSON;

This is a good try, but doesn't work, as the first expression of the coalesce is never NULL, because of the concat used with the space (|| ' ') used.

1. DECODE

select decode(TITLE, null, '', TITLE, TITLE || ' ') ||
       decode(FIRSTNAME, null, '', FIRSTNAME, FIRSTNAME || ' ') ||
       decode(MIDDLENAME, null, '', MIDDLENAME, MIDDLENAME || ' ') ||
       FAMILYNAME
       from PERSON;

This works but is a lot of code to do something very simple.

2. CASE

select (case
           when TITLE is null then ''
           else TITLE || ' ' end) ||
       (case
           when FIRSTNAME is null then ''
           else FIRSTNAME || ' ' end) ||
       (case
           when MIDDLENAME is null then ''
           else MIDDLENAME || ' ' end) ||
       FAMILYNAME
from PERSON;

This works but is a lot of code. Luckily the case statement, when indented properly, is quite readable and not at all bad.

3. NVL2

select nvl2(TITLE, TITLE || ' ', '') ||
       nvl2(FIRSTNAME, FIRSTNAME || ' ', '') ||
       nvl2(MIDDLENAME, MIDDLENAME || ' ', '') ||
       FAMILYNAME from PERSON;

This works but is hard to read. Requires knowledge of nvl2.

4. LTRIM

select ltrim(TITLE || ' ') ||
       ltrim(FIRSTNAME || ' ') ||
       ltrim(MIDDLENAME || ' ') ||
       FAMILYNAME from PERSON;

As far as I can tell, this is quite appropriate. The ltrim removes spaces at the beginning of the string. If the string contains only ' ', all ' ' are removed and we're home free.

It is short, but requires some knowledge of what ltrim does exactly.

Falls in the category of 'doing something clever'.

5. REGEXP_REPLACE

select regexp_replace(TITLE
        || ' ' || FIRSTNAME
        || ' ' || MIDDLENAME
        || ' ' || FAMILYNAME
        , ' +', ' ')
from person;

A colleague of mine came up with this little chestnut.

It's nice if you enjoy regular expressions. I do not.

Still, it's nice to be able to just concat everything together, and have the whole thing sorted out with one simple regular expression.

Addendum

The function wm_concat has been removed from Oracle PL/SQL and should not be used.

Which is a shame, as it does exactly what we want.

References

American Culture - Naming

https://culturalatlas.sbs.com.au/american-culture/american-culture-naming

Lalit Kumar B - Why not use WM_CONCAT function in Oracle?

https://lalitkumarb.wordpress.com/2015/04/29/why-not-use-wm_concat-function-in-oracle/

Updates: added regexp_replace solution