問題描述
我只有 MS SQL Server 2008 和 Visual Studio 的 Express 版本.鑒于我無法創(chuàng)建 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.當(dāng)它與上面的 SQL 連接時，它似乎轉(zhuǎn)義了那些控制字符，即.& 變成&放大器;而 \n 變成了 &#xOD，這不是我想要的.鑒于 col1 和連接字段將用于更新另一個表，那么以未轉(zhuǎn)義的原始形式獲取連接字段的最佳方法是什么，或者沒有，唯一的方法是求助于外部代碼?
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?
表架構(gòu)如下所示:StrConcat (id int 主鍵, col1 int, txt varchar(80))col1 上有一個索引，txt 應(yīng)該按 col1 分組，按組內(nèi)的 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.
推薦答案
行為當(dāng)然是 設(shè)計.如果不以這種方式轉(zhuǎn)義，則查詢可能會輸出無效的 XML.
The behaviour is, of course, by design. If it didn't escape this way, the query could output invalid XML.
如果您需要在 SQL Server 中獲取未轉(zhuǎn)義的值，則必須使用 其他連接方法(遞歸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).
當(dāng)然，如果將其直接傳遞給應(yīng)用程序，則在那里對其進行轉(zhuǎn)義會容易得多，即使用 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.
這篇關(guān)于MySQL 的 group_concat 和轉(zhuǎn)義字符串的 SQL Server 版本的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！