如何在 SQL Server 中连接字符串和逗号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5355428/
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
How to concatenate strings and commas in SQL Server?
提问by Chan
I'm relatively new to MSSQL, so sorry if the question might sounds trivial. I want to concatenate multiple fields with a delimiter ,. However, when the field is empty, the extra ,will be included in the result string as well. So is there an easy way to solve this problem? For example,
我对 MSSQL 比较陌生，如果这个问题听起来微不足道，我很抱歉。我想用分隔符连接多个字段,。但是，当该字段为空时，额外的内容,也将包含在结果字符串中。那么有没有简单的方法来解决这个问题呢？例如，
SELECT VRI.Street_Number_and_Modifier + ',' +
VRI.Street_Direction + ',' +
VRI.Street_Name + ',' +
VRI.Street_Direction + ',' +
VRI.Street_Suffix + ',' +
VRI.Street_Post_Direction + ',' +
VRI.Unit
FROM View_Report_Information_Tables VRI
采纳答案by Joel Beckham
If the columns are empty instead of null, you can try this:
如果列是空的而不是空的，你可以试试这个：
SELECT VRI.Street_Number_and_Modifier
+ CASE WHEN VRI.Street_Number_and_Modifier <> '' THEN ', ' ELSE '' END
+ VRI.Street_Direction
+ CASE WHEN VRI.Street_Direction <> '' THEN ', ' ELSE '' END
+ VRI.Street_Name
+ CASE WHEN VRI.Street_Name <> '' THEN ', ' ELSE '' END
+ VRI.Street_Direction
+ CASE WHEN VRI.Street_Direction <> '' THEN ', ' ELSE '' END
+ VRI.Street_Suffix
+ CASE WHEN VRI.Street_Suffix <> '' THEN ', ' ELSE '' END
+ VRI.Street_Post_Direction
+ CASE WHEN VRI.Street_Post_Direction <> '' THEN ', ' ELSE '' END
+ VRI.Unit
+ CASE WHEN VRI.Unit<> '' THEN ', ' ELSE '' END
FROM View_Report_Information_Tables VRI
回答by Cade Roux
This modified version of Lamak's handles NULL or strings containing only space/empty:
Lamak 的这个修改版本处理 NULL 或只包含空格/空的字符串：
SELECT COALESCE(NULLIF(VRI.Street_Number_and_Modifier, '') + ',', '') +
COALESCE(NULLIF(VRI.Street_Direction, '') + ',', '') +
COALESCE(NULLIF(VRI.Street_Name, '') + ',', '') +
COALESCE(NULLIF(VRI.Street_Direction, '') + ',', '') +
COALESCE(NULLIF(VRI.Street_Suffix, '') + ',', '') +
COALESCE(NULLIF(VRI.Street_Post_Direction, '') + ',', '') +
COALESCE(NULLIF(VRI.Unit, ''), '')
FROM View_Report_Information_Tables VRI
回答by mtavares
I was able to get it to work with a slightly different approach. Putting the commas at the beginning of each field and then removing the first one with the STUFF function worked for me:
我能够用稍微不同的方法让它工作。将逗号放在每个字段的开头，然后使用 STUFF 函数删除第一个对我有用：
SELECT
STUFF((COALESCE(', ' + NULLIF(VRI.Street_Number_and_Modifier, ''), '') +
COALESCE(', ' + NULLIF(VRI.Street_Direction, ''), '') +
COALESCE(', ' + NULLIF(VRI.Street_Name, ''), '')) +
COALESCE(', ' + NULLIF(VRI.Street_Direction, ''), '')) +
COALESCE(', ' + NULLIF(VRI.Street_Suffix, ''), '')) +
COALESCE(', ' + NULLIF(VRI.Street_Post_Direction, ''), '')) +
COALESCE(', ' + NULLIF(VRI.Unit, ''), ''))
, 1, 2, '')
FROM View_Report_Information_Tables AS VRI
回答by Hayder Nahee
For SQL 2008+
对于SQL 2008+
Using ISNULL(Colmn1 + ', ', '') Will always result with a leading comma in the end, so you'll have to handle it. Example:
使用 ISNULL(Colmn1 + ', ', '') 将始终以逗号结尾，因此您必须处理它。例子：
DECLARE @Column1 NVARCHAR(10) = 'Column1'
, @Column2 NVARCHAR(10) = 'Column2'
SELECT SUBSTRING( ISNULL(@Column1 + ', ', '') + ISNULL(@Column2 + ', ', '')
, 0 --Starting from 0 not 1 to remove leading comma
, LEN(ISNULL(@Column1 + ', ', '') + ISNULL(@Column2 + ', ', '')))
Or we could approach this the other way around and use the STUFF function to remove our beginning comma which looks cleaner, example:
或者我们可以用另一种方式来解决这个问题，并使用 STUFF 函数删除我们的开头逗号，这看起来更干净，例如：
SELECT STUFF (ISNULL(( ', ' + @Column1), '') + ISNULL(( ', ' + @Column2), ''), 1, 2, N'')
For SQL 2012+we could use the CONCAT function and remove beginning comma using STUFF similar to our previous example but avoiding ISNULL:
对于SQL 2012+，我们可以使用 CONCAT 函数并使用 STUFF 删除开头的逗号，类似于我们之前的示例，但要避免 ISNULL：
SELECT STUFF(CONCAT( ', ' + @Column1, ', ' + @Column2), 1, 2, N'')
For SQL 2017+CONCAT_WS was introduced where you can concatinate/join multiple string columns with a delimiter specified in the first argument of the function:
对于SQL 2017+，引入了 CONCAT_WS，您可以在其中使用函数的第一个参数中指定的分隔符连接/连接多个字符串列：
MS Doc Example:
MS 文档示例：
SELECT CONCAT_WS(',' --delimiter
,'1 Microsoft Way', NULL, NULL, 'Redmond', 'WA', 98052) AS Address;
回答by Lamak
Try this:
尝试这个：
SELECT COALESCE(VRI.Street_Number_and_Modifier + ',','') +
COALESCE(VRI.Street_Direction + ',','') +
COALESCE(VRI.Street_Name + ',','') +
COALESCE(VRI.Street_Direction + ',','') +
COALESCE(VRI.Street_Suffix + ',','') +
COALESCE(VRI.Street_Post_Direction + ',','') +
COALESCE(VRI.Unit,'')
FROM View_Report_Information_Tables VRI
回答by Jamiec
Short or long answer?
简短回答还是长回答？
Short answer - dont. This is a formatting issue, not a database issue.
简短的回答 - 不要。这是格式问题，不是数据库问题。
Long answer - When you concatenate a string and a null in sql server, the result is null. So you can use combinations of ISNULL
长答案 - 当您在 sql server 中连接字符串和空值时，结果为空值。所以你可以使用 ISNULL 的组合
SELECT ISNULL(afield + ',','') + ISNULL(bfield + ',','')
回答by Stefan Steiger
You have to use select case when IsNull(fieldname, '')= '' or ltrim(rtrim(fieldname))='') Then ... Else... end +...
当 IsNull(fieldname, '')= '' 或 ltrim(rtrim(fieldname))='') Then ... Else... end +...
Edit:
Was written from Android mobile.
Below your example.
The following translations (from German) apply, FYI:
编辑：
是从 Android 手机编写的。
在你的例子下面。
以下翻译（来自德语）适用，仅供参考：
Vorname: given name
Name: surname
Benutzer: User
Vorname: 名字
Name: surname
Benutzer: User
And here's the example code:
这是示例代码：
CREATE VIEW [dbo].[V_RPT_SEL_Benutzer]
AS
SELECT
BE_ID AS RPT_UID,
CASE
WHEN (ISNULL(BE_Name, '0') = '0' OR LTRIM(RTRIM(BE_Name)) = '') AND (ISNULL(BE_Vorname, '0') = '0' OR LTRIM(RTRIM(BE_Vorname)) = '')
THEN ''
WHEN (ISNULL(BE_Name, '0') = '0' OR LTRIM(RTRIM(BE_Name)) = '')
THEN ISNULL(BE_Vorname, '')
WHEN (ISNULL(BE_Vorname, '0') = '0' OR LTRIM(RTRIM(BE_Vorname)) = '')
THEN ISNULL(BE_Name, '')
ELSE
ISNULL(BE_Name, '') + ', ' + ISNULL(BE_Vorname, '')
END AS RPT_Name,
ROW_NUMBER() OVER (ORDER BY BE_Name, BE_Vorname ASC) AS RPT_Sort
FROM T_Benutzer
回答by ses011
I would agree completely with Jamiec's short answer.
我完全同意 Jamiec 的简短回答。
Otherwise, I would look at a nasty solution of using a REPLACE([concat], ',,', ',') everywhere you concatenate two columns, and then figure out how to trim commas from the beginning and end of the string where the first and last columns might be empty. Very very messy.
否则，我会看看在连接两列的任何地方使用 REPLACE([concat], ',,', ',') 的讨厌的解决方案，然后弄清楚如何从字符串的开头和结尾修剪逗号，其中第一列和最后一列可能为空。非常非常凌乱。
回答by Dumitrescu Bogdan
SELECT isnull(VRI.Street_Number_and_Modifier + ',','')+
isnull(VRI.Street_Direction + ',','')+
isnull(VRI.Street_Name + ',','')+
isnull(VRI.Street_Direction + ',','')+
isnull(VRI.Street_Suffix + ',','')+
isnull(VRI.Street_Post_Direction + ',','')+
isnull(VRI.Unit,'')
FROM View_Report_Information_Tables VRI
回答by Andrew
You could use the ISNULL(field + ',', '')
你可以使用 ISNULL(field + ',', '')
