什么是连接分隔符的 SQL Server 函数 [英] What is SQL Server function to concatenate with separator

问题描述

我正在寻找等效于 的 SQL ServerMySQL concat_ws 用分隔符连接字符串.没找到，SQL server 有没有一个函数可以实现这个任务?

I am looking for SQL Server equivalent of MySQL concat_ws to concatenate strings with separator. I could not find one, is there a single function in SQL server to achieve this task?

推荐答案

没有真正的等价物(即使引用的答案也不完全等价).您通常会在每个值之间添加分隔符:

There is no real equivalent (even the referenced answer is not exactly equivalent). You would normally add the separators between each value:

select A + ',' + B + . . .

一个主要区别是 concat_ws() 会跳过 NULL 参数，但 + 不会.您可以通过以下方式模拟此行为:

One primary difference is that concat_ws() will skip NULL arguments, but + will not. You could emulate this behavior with:

select stuff((coalesce(',' + A, '') + coalesce(',' + B, '') + . . .
             ), 1, 1, '')

当然，这不会像 concat_ws() 那样将值转换为字符串.所以，更接近的版本是这样的:

Of course, this doesn't convert the values to strings, as concat_ws() does. So, a closer version is something like:

select stuff((coalesce(',' + cast(A as varchar(255)), '') +
              coalesce(',' + cast(B as varchar(255)), '') +
              . . .
             ), 1, 1, '')

您可以对引用的答案应用相同的修复:

You can apply the same fixes to the referenced answer:

SELECT id,
       STUFF((SELECT ';' + cast(v as varchar(255))
              FROM (VALUES (a), (b), (c), (d)) AS v (v)
              WHERE v is not null
              FOR XML PATH (''), TYPE
             ).value('.[1]', 'varchar(max)'),
             1, 1, ''
            )
FROM foo
ORDER BY id;

这篇关于什么是连接分隔符的 SQL Server 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！