How to escape square brackets inside square brackets for field name

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]

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开发者_StackOverflow中文版?

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

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

Returns

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

So all closing square brackets need to be doubled up.

Hang a ] on the end of inline []

   SELECT [Eggs[scrambled]] or Bacon[fried]]] FROM Yummy

SET QUOTED_IDENTIFIER is ON by default so no need to escape anything

SELECT 1 AS "[Eggs[scrambled] or Bacon[fried]]"

In an SSIS SQL statement querying an excel sheet you must substitute square brackets for parenthesis to select individual columns. For example to select "My Column [Great Column]" you would SELECT [My Column (Great Column)] FROM [$A1:AX10000]