SQL 在连接上使用 If Not Null

SQL using If Not Null on a Concatenation

提问by BiscuitCookie

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

我怎么能放一种 IF Middlename = NULL 语句，以便它只显示 John-Smith

回答by Andrew Wolfe

Here would be my suggestions:

以下是我的建议：

PostgreSQL and other SQL databases where 'a' || NULL IS NULL, then use COALESCE:

PostgreSQL 和其他 SQL 数据库 where 'a' || NULL IS NULL，然后使用COALESCE：

SELECT firstname || COALESCE('-' || middlename, '') || '-' || surname ...

Oracle and other SQL databases where 'a' || NULL = 'a':

Oracle 和其他 SQL 数据库，其中'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.

我喜欢简洁。中间名是否为空对于任何维护程序员来说都不是很有趣。CASE 开关非常好，但它们笨重。我想尽可能避免重复相同的列名（“中间名”）。

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 NULLyields a NULLor not.

正如@Prdp 所指出的，答案是特定于 RDBMS 的。具体是服务器是否将零长度字符串视为等价于NULL，这决定了连接 a 是否NULL产生 a NULL。

Generally COALESCEis most concise for PostgreSQL-style empty string handling, and DECODE (*VALUE*, NULL, ''...for Oracle-style empty string handling.

通常COALESCE对于 PostgreSQL 样式的空字符串处理和DECODE (*VALUE*, NULL, ''...Oracle 样式的空字符串处理最为简洁。

回答by a_horse_with_no_name

If you use Postgres, concat_ws()is what you are looking for:

如果您使用 Postgres，concat_ws()就是您要查找的内容：

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

SQLFiddle: http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/8812

SQLFiddle：http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/8812

To treat empty strings or strings that only contain spaces like NULLuse nullif():

要处理空字符串或仅包含空格的字符串，请NULL使用nullif()：

 SELECT concat_ws('-', Firstname, nullif(trim(Middlename), ''), Surname)  AS example_column
 FROM example_table

回答by Michael Buen

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

实时代码：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:

正如我所暗示的那样，有人会给出一个不在问题中的场景。所以要让它使用空的中间名。只需为空字符串添加一个 nullif：

select first_name || coalesce('-' || nullif(middle_name,'') , '') || '-' || last_name 
from t;

Output:

输出：

|        ?column? |
|-----------------|
|      john-smith |
|      obi-kinobi |
| jane-anne-smith |

回答by Shoeless

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.

由于与字符串连接的 NULL 会产生 NULL，我们可以尝试构建我们的子字符串并用空字符串替换 NULL，然后将其连接到名称的下一部分。

This assumes that FirstName and Surname are always NOT NULL, but you could apply the same logic to then as well.

这假设 FirstName 和 Surname 始终为 NOT NULL，但您也可以对 then 应用相同的逻辑。

回答by Pham X. Bach

One solution could be using case statement

一种解决方案可能是使用 case statement

select case Middlename is not null then (Firstname || '-' || Middlename || '-' || Surname) 
    else (Firstname || '-' || Surname) end AS example_column
from ....

回答by P?????

You can use CASEstatement

您可以使用CASE声明

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 NULLuse Middlename IS NOT NULLinstead of Middlename <> ''

根据您的示例数据，我已检查empty string. 检查NULL使用Middlename IS NOT NULL而不是Middlename <> ''

回答by Lukasz Szozda

You could use REPLACE(if Oracle)

您可以使用REPLACE（如果是 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

对于downvoter

OP clearly said that:

OP明确表示：

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

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

John--Smith

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

这将显示名字-中间名-姓氏，例如

约翰·史密斯

So:

所以：

1. Middlenameis blank: ''this solution works in SQLite/PostgreSQL/Oracle
2. Middlenameis NULLand OP probably uses Oracle

1. Middlename为空白：''此解决方案适用于SQLite/PostgreSQL/Oracle
2. Middlename是NULL和 OP 可能使用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.

尽管 Oracle 将零长度字符串视为空值，但将零长度字符串与另一个操作数连接总是会产生另一个操作数，因此空值只能由两个空字符串连接产生。

||concatentaion operator:

||串联运算符：

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


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

回答by Stavr00

• The NULLIFexpression reduces blank Middlenameto NULL
• Concatenating '-'with a NULLwill always return NULL
• TheVALUEexpression replaces NULLs with an empty string

• 该NULLIF表达式将空白减少Middlename为NULL
• '-'与 a连接NULL将始终返回NULL
• 该VALUE表达式将NULLs替换为空字符串

_

_

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