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
