SQL using If Not Null on a Concatenation

问题:

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

回答1:

Here would be my suggestions:

PostgreSQL and other SQL databases where 'a' || NULL IS NULL:

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.

回答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

回答3:

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.

回答4:

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 |

回答5:

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

回答6:

You can use CASE statement

select Firstname 
      || case when Middlename <> '' Then '-'||Middlename  else '' END 
      || case when Surname<> '' Then '-'||Surname else '' END

As per your sample data I have check for empty string. To check NULL use Middlename IS NOT NULL instead of Middlename <> ''

回答7:

• The NULLIF expression reduces blank Middlename to NULL
• Concatenating '-' with a NULL will always return NULL
• TheVALUE expression replaces NULLs with an empty string

_

SELECT Firstname || VALUE( '-' || NULLIF('Middlename',''),'') || '-' || Surname'  
       AS example_column
FROM example_table

回答8:

You could use REPLACE (if Oracle)

SELECT
   REPLACE(Firstname || '-' || Middlename || '-' || Surname,'--','-') 
   AS example_column
FROM example_table;

Warning: I've assumed there is no valid name with - as first or last character.

---

For downvoter

OP clearly said that:

SELECT (Firstname || '-' || Middlename || '-' || Surname)  AS example_column
FROM example_table

This will display Firstname-Middlename-Surname e.g.

John--Smith

So:

1. Middlename is blank: '' this solution works in SQLite/PostgreSQL/Oracle
2. Middlename is NULL and OP probably uses Oracle

Although Oracle treats zero-length character strings as nulls, concatenating a zero-length character string with another operand always results in the other operand, so null can result only from the concatenation of two null strings.

|| concatentaion operator:

 -- PostgreSQL/SQLite
 SELECT 'sth' || NULL
 -- NULL


 -- Oracle
 SELECT 'sth' || NULL
 -- sth