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
In SQL Server, NULL is a special pointer that specifies a value that is undefined or does not exist. When passing a NULL value as a parameter to the CONCAT function, the NULL values are converted to an empty string.
The IS NOT NULL condition is used in SQL to test for a non-NULL value. It returns TRUE if a non-NULL value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
Using the String.concat() method is a good choice when we want to concatenate String objects. The empty String returned by the getNonNullString() method gets concatenated to the result, thus ignoring the null objects.
Concatenating Data When There Are NULL ValuesTo resolve the NULL values in string concatenation, we can use the ISNULL() function. In the below query, the ISNULL() function checks an individual column and if it is NULL, it replaces it with a space.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With