SQL 在连接上使用 If Not Null [英] SQL using If Not Null on a Concatenation

问题描述

如果我有桌子

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

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

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

John--Smith
Jane-Anne-Smith

第二个(Jane 的)显示正确，但是由于 John 没有中间名，我希望它忽略第二个破折号.

The second one (Jane’s) displays correct, however since John doesn’t have a middlename, I want it to ignore the second dash.

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

How could I put a sort of IF Middlename = NULL statement in so that it would just display John-Smith

推荐答案

以下是我的建议:

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

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

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

Oracle 和其他 SQL 数据库 where 'a' ||NULL = 'a':

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

SELECT first name || DECODE(middlename, NULL, '', '-' || middlename) || '-' || surname...

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

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.

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

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.

通常 COALESCE 对于 PostgreSQL 风格的空字符串处理最为简洁，DECODE (*VALUE*, NULL, ''... 对于 Oracle 风格的空字符串处理处理.

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

这篇关于SQL 在连接上使用 If Not Null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！