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

时间:2022-06-21 03:03:55

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]]"