与 SQL 中的 NULL 值连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8233746/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Concatenate with NULL values in SQL
提问by LuckySlevin
Column1 Column2
------- -------
apple juice
water melon
banana
red berry
I have a table which has two columns. Column1 has a group of words and Column2 also has a group of words. I want to concatenate them with + operator without a space.
我有一个有两列的表。Column1 有一组词，Column2 也有一组词。我想将它们与 + 运算符连接起来，没有空格。
For instance: applejuice
例如：苹果汁
The thing is, if there is a null value in the second column, i only want to have the first element as a result.
问题是，如果第二列中有一个空值，我只想得到第一个元素作为结果。
For instance: banana
例如：香蕉
Result
------
applejuice
watermelon
banana
redberry
However, when i use column1 + column2, it gives a NULL value if Comunm2 is NULL. I want to have "banana" as the result.
但是，当我使用 column1 + column2 时，如果 Comunm2 为 NULL，它会给出一个 NULL 值。结果我想要“香蕉”。
回答by Joe Stefanelli
Use the COALESCE function to replace NULL values with an empty string.
使用 COALESCE 函数用空字符串替换 NULL 值。
SELECT Column1 + COALESCE(Column2, '') AS Result
FROM YourTable
回答by Chris Cooper
You can do a union:
你可以做一个union：
(SELECT Column1 + Column2 FROM Table1 WHERE Column2 is not NULL)
UNION
(SELECT Column1 FROM Table1 WHERE Column2 is NULL);
回答by harsh
You can use a case condition:
您可以使用案例条件：
case when column_2 is not null
then concatenate
else column_1
end
回答by Client Relations
A few posts I have made tagged MSSQL have been renamed to 'SQL' by a moderator. So I am assuming you are using MSSQL
我发表的一些标记为 MSSQL 的帖子已被版主重命名为“SQL”。所以我假设你使用的是 MSSQL
COALESCE will return the FIRST non-null value.
COALESCE 将返回第一个非空值。
SELECT COALESCE('a', NULL, 'c')
will only return 'a'
只会返回 'a'
If you want Firstname + Lastname, where sometimes one or the other is NULL, use CONCAT. Concat adds the strings together and replaces NULLS with 0 length non-null value.
如果您想要名字 + 姓氏，有时其中一个为 NULL，请使用 CONCAT。Concat 将字符串相加并用长度为 0 的非空值替换 NULLS。
SELECT CONCAT('a', NULL, 'c')
will return 'ac'
将返回'ac'
If you want Fn space + middle name space + LN, combine concatinate with CONCAT:
如果你想要 Fn 空间 + 中间名空间 + LN，结合 concatinate 和 CONCAT：
SELECT CONCAT('a' + ' ', NULL + ' ', 'c')
Will return 'a c'.
将返回'a c'。
The space after middlename (null) is eliminated with the + and NULL.
中间名 (null) 后面的空格用 + 和 NULL 消除。
NULL + ' ' is null.
NULL + ' ' 为空。
So in cases where Middlename or Firstname is null, you won't get extra unwanted spaces.
因此，在 Middlename 或 Firstname 为空的情况下，您不会得到额外的不需要的空格。
回答by Chris E
I'm not certain what you're using as your database, but I would look for a "coalesce" function for your particular SQL dialect and use that.
我不确定您使用什么作为数据库，但我会为您的特定 SQL 方言寻找“合并”函数并使用它。
回答by Chris
The + sign for concatenation in TSQL will by default combine string + null to null as an unknown value.
TSQL 中用于连接的 + 号默认将 string + null 与 null 组合为一个未知值。
You can do one of two things, you can change this variable for the session which controlls what Sql should do with Nulls
您可以执行以下两项操作之一，您可以更改会话的此变量，该变量控制 Sql 应该对 Nulls 执行的操作
http://msdn.microsoft.com/en-us/library/ms176056.aspx
http://msdn.microsoft.com/en-us/library/ms176056.aspx
Or you can Coalesce each column to an empty string before concatenating.
或者您可以在连接之前将每一列合并为一个空字符串。
COALESCE(Column1, '')
回答by Jonathan Leffler
Standard SQL requires that string concatenation involving a NULL generates a NULL output, but that is written using the ||operation:
标准 SQL 要求涉及 NULL 的字符串连接生成 NULL 输出，但使用以下||操作编写：
SELECT a || b
FROM SomeTable;
The output will be null if either aor bor both contains a NULL.
输出将是零，如果任一a或b或两者包含NULL。
Using +to concatenate strings indicates that you are using a DBMS-specific extension. The behaviour might be the same as the standard requires - indeed, that seems to be the gist of your question.
使用+连接字符串表明您正在使用DBMS专用的扩展。行为可能与标准要求相同 - 实际上，这似乎是您问题的要点。
Some DBMS - notably Oracle - tend to treat null strings as equivalent to empty strings; then you can concatenate away merrily. However, that behaviour is not strictly standard-compliant if the ||operator is used.
一些 DBMS——特别是 Oracle——倾向于将空字符串视为等同于空字符串；然后你可以愉快地连接起来。但是，如果使用||运算符，则该行为并不严格符合标准。
Consider using COALESCE or NVL or IFNULL or some similar function to map the NULL to an empty string before concatenating.
考虑使用 COALESCE 或 NVL 或 IFNULL 或一些类似的函数在连接之前将 NULL 映射到空字符串。
回答by CapelliC
If you are using MySq, use ifnull(Column2, '')
如果您使用的是 MySq，请使用 ifnull(Column2, '')
