MySQL 的 group_concat 和转义字符串的 SQL Server 版本

问题描述

我只有 MS SQL Server 2008 和 Visual Studio 的 Express 版本.鉴于我无法创建 SQL Server 项目，因此无法使用 CLR 解决方案，我尝试使用

I only have the Express versions of MS SQL Server 2008 and Visual Studio. Given that I can't create a SQL Server project and therefore CLR solutions are out of the question, I've attempted to use

select col1, stuff( ( select ' ' + col2
from StrConcat t1
where t2.col1 = t1.col1
for xml path('')
),1,1,'')
from StrConcat t2
group by col1
order by col1

获取连接 col2 的行.col2 是一个 varchar 字段，带有一些控制字符，例如 & 和 \n.当它与上面的 SQL 连接时，它似乎转义了那些控制字符，即.& 变成&放大器;而 \n 变成了 &#xOD，这不是我想要的.鉴于 col1 和连接字段将用于更新另一个表，那么以未转义的原始形式获取连接字段的最佳方法是什么，或者没有，唯一的方法是求助于外部代码?

to get a row concatenated col2. col2 is a varchar field with some control characters like & and \n. When it is concatenated with the above SQL, it appears to escape those control characters ie. & becomes & amp ; and \n becomes &#xOD, which is not what I want it to do. Given that the col1 and concatenated field is going to be used to update another table, what is the best way to get the concatenated field in its unescaped original form or is there none and the only way is to resort to external code?

表架构如下所示:StrConcat (id int 主键, col1 int, txt varchar(80))col1 上有一个索引，txt 应该按 col1 分组，按组内的 id 排序.

The table schema looks like this: StrConcat (id int primary key, col1 int, txt varchar(80)) col1 has an index on it, txt should be grouped by col1, ordered by id within the group.

推荐答案

行为当然是 设计.如果不以这种方式转义，则查询可能会输出无效的 XML.

The behaviour is, of course, by design. If it didn't escape this way, the query could output invalid XML.

如果您需要在 SQL Server 中获取未转义的值，则必须使用 其他连接方法(递归CTE通常是仅次于CLR聚合和FOR XML，但最难写).

If you need to get an unescaped value within SQL Server, then you'll have to use one of the other concatenation methods (recursive CTE is usually the next-most efficient after CLR aggregate and FOR XML, but hardest to write).

当然，如果将其直接传递给应用程序，则在那里对其进行转义会容易得多，即使用 XmlReader .NET 中的类.

Of course, if this is being passed directly to an application, it will be much easier to unescape it there, i.e. using the XmlReader class in .NET.

这篇关于MySQL 的 group_concat 和转义字符串的 SQL Server 版本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！