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
Advertisement
Answer
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.