SQL CONCAT'ing NULL 字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1035819/
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
CONCAT'ing NULL fields
提问by Thomas R
I have a table with three fields, FirstName, LastName and Email.
我有一个包含三个字段的表，名字、姓氏和电子邮件。
Here's some dummy data:
这是一些虚拟数据：
FirstName | LastName | Email
Adam West [email protected]
Joe Schmoe NULL
Now, if I do:
现在，如果我这样做：
SELECT CONCAT(FirstName, LastName, Email) as Vitals FROM MEMBERS
Vitals for Joe is null, as there is a single null field. How do you overcome this behaviour? Also, is this the default behaviour in MS SQL Server?
Joe 的 Vitals 为空，因为只有一个空字段。你如何克服这种行为？另外，这是 MS SQL Server 中的默认行为吗？
回答by Stefan Mai
Try
尝试
ISNULL(FirstName, '<BlankValue>') -- In SQL Server
IFNULL(Firstname, '<BlankValue>') -- In MySQL
So,
所以，
CONCAT(ISNULL(FirstName,''),ISNULL(LastName,''),ISNULL(Email,'')) -- In SQL Server
CONCAT(IFNULL(FirstName,''),IFNULL(LastName,''),IFNULL(Email,'')) -- In MySQL
would return the same thing without the null issue (and a blank string where nulls should be).
将在没有空问题的情况下返回相同的东西（以及空字符串应该是空的）。
回答by BILBO
Look at CONCAT_WS
看着 CONCAT_WS
For example:
例如：
CONCAT_WS('',NULL,"TEST STRING","TEST STRING 2")
Yields
产量
TEST STRINGTEST STRING 2
TEST STRINGTEST STRING 2
This is easier than constructing IFNULLaround everything. You can use an empty string as the separator.
这比IFNULL围绕一切构建更容易。您可以使用空字符串作为分隔符。
回答by chaladi
In mysql isnull wont work some time. try IFNULL(),
在 mysql 中 isnull 不会工作一段时间。试试 IFNULL(),
CONCAT(IFNULL(FirstName,''),IFNULL(LastName,''),IFNULL(Email,''))
回答by Hafthor
SELECT ISNULL(FirstName,'')+ISNULL(LastName,'')+ISNULL(Email,'') as Vitals FROM MEMBERS
is recommended, but if you are really hooked on CONCAT, wrap it in {fn } and you can use the ODBC function like:
推荐使用，但如果您真的很喜欢 CONCAT，请将其包装在 {fn} 中，您可以使用 ODBC 函数，例如：
SELECT {fn CONCAT(ISNULL(FirstName,''), ISNULL(LastName,''), ISNULL(Email,''))} as Vitals FROM MEMBERS
If you need first<space>last but just last when first is null you can do this:
如果您需要 first<space>last 但在 first 为 null 时只需要 last，您可以这样做：
ISNULL(FirstName+' ','') + ISNULL(LastName,'')
I added the space on firstname which might be null -- that would mean the space would only survive if FirstName had a value.
我在 firstname 上添加了可能为空的空格——这意味着只有在 FirstName 有值时，该空格才会存在。
To put them all together with a space between each:
把它们放在一起，每个之间有一个空格：
RTRIM(ISNULL(Firstname+' ','') + ISNULL(LastName+' ','') + ISNULL(Email,''))
回答by Gabriele Petrioli
You can always use the CONCAT_NULL_YIELDS_NULLsetting..
您可以随时使用该CONCAT_NULL_YIELDS_NULL设置..
just run SET CONCAT_NULL_YIELDS_NULL OFFand then all nullconcatenations will result in text and not null..
只需运行SET CONCAT_NULL_YIELDS_NULL OFF，然后所有null连接都将导致文本而不是空..
回答by jmarceli
If you get (like I do in MySQL):
如果你得到（就像我在 MySQL 中所做的那样）：
#1582 - Incorrect parameter count in the call to native function 'ISNULL'
You can replace ISNULL function by COALESCE:
您可以用 COALESCE 替换 ISNULL 函数：
CONCAT(COALESCE(FirstName,''),COALESCE(LastName,''),COALESCE(Email,''))
回答by Brian
Stefan's answer is correct. To probe a little bit deeper you need to know that NULL is not the same as Nothing. Null represents the absence of a value, or in other words, not defined. Nothing represents an empty string which IS in fact a value.
斯蒂芬的回答是正确的。为了更深入地探索，您需要知道 NULL 与 Nothing 不同。Null 表示没有值，或者换句话说，没有定义。Nothing 代表一个空字符串，它实际上是一个值。
Undefined + anything = undefined
未定义 + 任何东西 = 未定义
Good database tidbit to hold onto!
不错的数据库花絮！
回答by Michael Freidgeim
Starting from MS SQL Server 2012 it was introduced CONCAT function and according to MSDN
从 MS SQL Server 2012 开始，它引入了 CONCAT 功能，根据 MSDN
Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned.
空值被隐式转换为空字符串。如果所有参数都为空，则返回 varchar(1) 类型的空字符串。
so it's enough to use CONCAT without IsNull
所以在没有 IsNull 的情况下使用 CONCAT 就足够了
CONCAT(FirstName, LastName, Email)
回答by Cade Roux
SQL Server does not have a CONCATfunction.
(Update: Starting from MS SQL Server 2012 it was introduced CONCAT function)
SQL Server 没有CONCAT功能。
（更新：从 MS SQL Server 2012开始引入了 CONCAT 功能）
In the default SQL Server behavior, NULLs propagate through an expression.
在默认的 SQL Server 行为中，NULL 通过表达式传播。
In SQL Server, one would write:
在 SQL Server 中，可以这样写：
SELECT FirstName + LastName + Email as Vitals FROM MEMBERS
If you need to handle NULLs:
如果您需要处理NULLs：
SELECT ISNULL(FirstName, '') + ISNULL(LastName, '') + ISNULL(Email, '') as Vitals FROM MEMBERS
回答by vaibhav sarode
In the case of MS Access
在 MS Access 的情况下
Option 1) SELECT (FirstName + " " + LastName + " " + Email) as Vitals FROM MEMBERS You will get blank result in the case of any field with null.
选项 1) SELECT (FirstName + " " + LastName + " " + Email) as Vitals FROM MEMBERS 如果任何字段为空，您将得到空白结果。
Option 2) SELECT (FirstName & " " & LastName & " " & Email) as Vitals FROM MEMBERS You will get Space in place of field with null.
选项 2) SELECT (FirstName & " " & LastName & " " & Email) as Vitals FROM MEMBERS 您将获得 Space 代替字段为 null。
