SQL using If Not Null on a Concatenation
Solution 1
Here would be my suggestions:
PostgreSQL and other SQL databases where 'a' || NULL IS NULL
, then use COALESCE:
SELECT firstname || COALESCE('-' || middlename, '') || '-' || surname ...
Oracle and other SQL databases where 'a' || NULL = 'a'
:
SELECT first name || DECODE(middlename, NULL, '', '-' || middlename) || '-' || surname...
I like to go for conciseness. Here it is not very interesting to any maintenance programmer whether the middle name is empty or not. CASE switches are perfectly fine, but they are bulky. I'd like to avoid repeating the same column name ("middle name") where possible.
As @Prdp noted, the answer is RDBMS-specific. What is specific is whether the server treats a zero-length string as being equivalent to NULL
, which determines whether concatenating a NULL
yields a NULL
or not.
Generally COALESCE
is most concise for PostgreSQL-style empty string handling, and DECODE (*VALUE*, NULL, ''...
for Oracle-style empty string handling.
Solution 2
If you use Postgres, concat_ws()
is what you are looking for:
SELECT concat_ws('-', Firstname, Middlename, Surname) AS example_column
FROM example_table
SQLFiddle: http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/8812
To treat empty strings or strings that only contain spaces like NULL
use nullif()
:
SELECT concat_ws('-', Firstname, nullif(trim(Middlename), ''), Surname) AS example_column
FROM example_table
Solution 3
This approach works:
select first_name || coalesce('-' || middle_name, '') || '-' || last_name
from t;
Output:
| ?column? |
|-----------------|
| john-smith |
| jane-anne-smith |
UPDATE
Live code: http://sqlfiddle.com/#!15/d5a1f/1
Just as my inkling, someone will give a scenario that is not in the question. So to make it work with empty middle name. Just add a nullif for empty string:
select first_name || coalesce('-' || nullif(middle_name,'') , '') || '-' || last_name
from t;
Output:
| ?column? |
|-----------------|
| john-smith |
| obi-kinobi |
| jane-anne-smith |
Solution 4
One solution could be using case statement
select case Middlename is not null then (Firstname || '-' || Middlename || '-' || Surname)
else (Firstname || '-' || Surname) end AS example_column
from ....
Solution 5
This may be a viable option:
SELECT FirstName || '-' || ISNULL(MiddleName + '-', '') || Surname
Since a NULL concatenated with a string yields a NULL, we can attempt to build our sub-string and replace a NULL with an empty string, which is then concatenated to the next part of the name.
This assumes that FirstName and Surname are always NOT NULL, but you could apply the same logic to then as well.
BiscuitCookie
Updated on May 26, 2020Comments
-
BiscuitCookie almost 4 years
If I have the table
SELECT (Firstname || '-' || Middlename || '-' || Surname) AS example_column FROM example_table
This will display Firstname-Middlename-Surname e.g.
John--Smith Jane-Anne-Smith
The second one (Jane’s) displays correct, however since John doesn’t have a middlename, I want it to ignore the second dash.
How could I put a sort of IF Middlename = NULL statement in so that it would just display John-Smith