将两列的数据合并为 SQL 中的一列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8158310/
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
Combining two column's data into one column in SQL?
提问by James123
I am trying to combine Last name, first name and middle name into single sort name column in a SQL statement. Sometime middle name will be NULL, and if that's the case, the sort name is showing NULL.
我正在尝试将姓氏、名字和中间名组合到 SQL 语句中的单个排序名称列中。有时中间名会是NULL，如果是这种情况，排序名称会显示NULL。
How to handle this?
如何处理？
SELECT TOP 500
Last_Name, First_Name, Middle_Name,
[Last_Name] + ',' + [First_Name] + ' ' + [Middle_Name] AS SORT_NAME
FROM [dbo].[usr_CUSTOMER]
ORDER BY SORT_NAME
Results:
结果：
Last_Name First_Name MiddleName Sort_Name
Aa Robert NULL NULL
But I want to see sort_name to be 'Aa,Robert'.
但我希望看到 sort_name 为'Aa,Robert'.
回答by drdwilcox
合并：
COALESCE([Last_Name], '') + ',' + COALESCE([First_Name], '') + ' ' +
COALESCE(
[Middle_Name], '') AS SORT_NAME
Of course, this will leave ugly commas when last name or both first and middle are empty, so your actual code will need to be a bit more clever.
当然，当姓氏或名字和中间名都为空时，这会留下难看的逗号，因此您的实际代码需要更聪明一些。
回答by marc_s
Use the ISNULL()function - it replaces a NULLvalue with something you define:
使用ISNULL()函数 - 它用NULL你定义的东西替换一个值：
SELECT TOP 500
Last_Name, First_Name, Middle_Name,
[Last_Name] + ',' + [First_Name] + ' ' + ISNULL([Middle_Name], '') as SORT_NAME
FROM [dbo].[usr_CUSTOMER]
ORDER BY SORT_NAME
回答by Andrew
You can use the ISNULL function
您可以使用 ISNULL 函数
ISNULL ( check_expression , replacement_value )
Docs : http://msdn.microsoft.com/en-us/library/ms184325.aspx
回答by Joe Stefanelli
Note that I moved the space separating First and Middle name inside the COALESCE so that you avoid having a trailing space when Middle name is NULL.
请注意，我在 COALESCE 中移动了分隔 First 和 Middle name 的空格，以便在 Middle name 为 NULL 时避免尾随空格。
..., [Last_Name]+ ','+ [First_Name] + COALESCE(' '+ [Middle_Name],'') as SORT_NAME...
回答by aashish
Try
尝试
CONCAT(firstname,' ', LastName,' ', MiddleName) AS Full_Name
