Menu
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?
830
The first backslash escapes the second one into the string, so that what regex sees is \] . Since regex just sees one backslash, it uses it to escape the square bracket. In regex, that will match a single closing square bracket. If you're trying to match a newline, for example though, you'd only use a single backslash.
On SQL Server and MS Access, square brackets have a special meaning when used in a query filter. The square brackets are used to specify a set or range of characters, as in "[A-Z]" which would match any single character from 'A' to 'Z'.
SQL: Finding square brackets using LIKE in SQL Server T-SQL The trick is that to find the opening bracket, you need to enclose it in a pair of square brackets. But you can just find the closing one directly. That returns no rows. That returns rows 1, 2, and 4 as expected.
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.
129
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
