I have some dynamic SQL that generates column names. A quick example might be something like this:
我有一些动态SQL生成列名。一个简单的例子是这样的:
SELECT dbo.getSomething(123) [Eggs[scrambled] or Bacon[fried]]
The final column name should be this:
最后的列名应该是:
Eggs[scrambled] or Bacon[fried]
鸡蛋(炒)或培根(炒)
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.
如果我尝试运行这个，它会在word上出现错误，或者(即使我用xyz替换它，它仍然会出现错误)。如果我取出方括号内的集合，问题就解决了。所以我的结论是，除非你以某种方式摆脱方括号，否则你不能嵌套方括号。
How do I escape them in this case?
在这种情况下，我怎样才能逃脱呢?
3 个解决方案
#1
18
You can use the quotename function to see the proper escaping.
您可以使用quotename函数来查看正确的转义。
select quotename('Eggs[scrambled] or Bacon[fried]')
Returns
返回
[Eggs[scrambled]] or Bacon[fried]]]
So all closing square brackets need to be doubled up.
所以所有的右方括号都需要加倍。
#2
8
Hang a ] on the end of inline []
在内联[]的末端挂一个]
SELECT [Eggs[scrambled]] or Bacon[fried]]] FROM Yummy
#3
3
SET QUOTED_IDENTIFIER is ON by default so no need to escape anything
设置QUOTED_IDENTIFIER是默认的，所以不需要逃避任何东西。
SELECT 1 AS "[Eggs[scrambled] or Bacon[fried]]"
#1
18
You can use the quotename function to see the proper escaping.
您可以使用quotename函数来查看正确的转义。
select quotename('Eggs[scrambled] or Bacon[fried]')
Returns
返回
[Eggs[scrambled]] or Bacon[fried]]]
So all closing square brackets need to be doubled up.
所以所有的右方括号都需要加倍。
#2
8
Hang a ] on the end of inline []
在内联[]的末端挂一个]
SELECT [Eggs[scrambled]] or Bacon[fried]]] FROM Yummy
#3
3
SET QUOTED_IDENTIFIER is ON by default so no need to escape anything
设置QUOTED_IDENTIFIER是默认的，所以不需要逃避任何东西。
SELECT 1 AS "[Eggs[scrambled] or Bacon[fried]]"