如何为字段名称转义方括号内的方括号

2022-01-01 00:00:00 sql escaping sql-server-2005 tsql sql-server

我有一些生成列名的动态 SQL.一个简单的例子可能是这样的:

I have some dynamic SQL that generates column names. A quick example might be something like this:

SELECT dbo.getSomething(123) [Eggs[scrambled] or Bacon[fried]]

最后的列名应该是这样的:

The final column name should be this:

鸡蛋[炒]或培根[炸]

Eggs[scrambled] or Bacon[fried]

如果我尝试运行它，它会在单词 OR 上出错(即使我用 xyz 替换它，它仍然会在该令牌上出错).如果我取出内部方括号集，问题就解决了.所以我的结论是你不能嵌套方括号，除非你以某种方式逃避它们.

If I try to run this it will error on the word OR (even if I replace it with xyz it still errors on that token). The problem is fixed if I take out the inner sets of square brackets. So my conclusion is that you can't nest square brackets unless you somehow escape them.

在这种情况下我如何逃避它们?

How do I escape them in this case?

推荐答案

您可以使用 quotename 函数来查看正确的转义.

You can use the quotename function to see the proper escaping.

select quotename('Eggs[scrambled] or Bacon[fried]')

退货

[Eggs[scrambled]] or Bacon[fried]]]

所以所有右方括号都需要加倍.

So all closing square brackets need to be doubled up.

相关文章