How to Escape Square Brackets Inside Square Brackets for Field Name

How to escape square brackets inside square brackets for field name

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.

How to escape square brackets in column alias?

You need to double them up, just like you would a single quote ('):

SELECT *
FROM [My]]Table];

You only need to do this with right brackets though, left don't need to be. For example:

SELECT *
FROM [My[Table];

Based on this statement however "but I am building some complex dynamic T-SQL statements and each column alias is enclosed with square brackets and using quotes if the alias contains brackets will make things a little bit harder." it seems like you're doing something like '... FROM [' + @TableName + '] ...'; Don't. Use QUOTENAME: '... FROM ' + QUOTENAME(@TableName) + '...'.

QUOTENAME properly quotes and escapes your variable. So, for the value '[MyTable]', it would return '[[MyTable]]]'. It also has a second, optional parameter, which can be used to quote input strings with other identifiers. For example, say the variable @String had the value "Don't", QUOTENAME(@String, '''') would return 'Don''t'.

How to escape square brackets inside a SP parameter

You need to quote you column's name correctly. So, for a column named silly[column]name you would use [silly[column]]name]. Note that the [ inside the name doesn't need be escaped, however the ] does (to ]]).

Square brackets inside the name of column in VBA query?

Coudn't find any documentation about that, so I did some experiments. I created a small table containing one column with exact your column name, executed a Select * from [Sheet1$] and had a look to the column name within the returned recordset. Turned out that the brackets where replaced by parenthesis:

Dim conn As ADODB.Connection, rs As ADODB.Recordset
Set conn = New ADODB.Connection
Dim connString As String
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Name & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
conn.Open connString
Set rs = conn.Execute("Select * from [Sheet1$]")

Dim i As Integer
For i = 0 To rs.Fields.Count
    Debug.Print rs.Fields(i).Name
Next

>> (KPI) Standard Delivery Capability SO (<0/0)

To query this field, you need to (a) enclose the field name with brackets and (b) replace the brackets within the field name with parenthesis:

dim fieldname as String, sql as String
fieldName = "[(KPI) Standard Delivery Capability SO (<0/0)]"

' Use field in result set:
sql = "Select " & fieldname & " from [Sheet1$]"
Set rs = conn.Execute(sql)

' Use field in Where-Clause:
sql = "Select * from [Sheet1$] where " & fieldname & " > 100"
Set rs = conn.Execute(sql)

In your case, where you want to execute a aggregate function on that field, you need to specify

WhatToSelect = "avg([(KPI) Standard Delivery Capability SO (<0/0)])"

How do I query column names that contain square brackets?

If the column names have square brackets, then you can use double quotes to wrap around the column names. Below sample was tested in SQL Server.

Script:

CREATE TABLE dbo.myTable
(
        "[name]" varchar(max) not null
    ,   "[height]" int not null
);

Querying all the columns:

SELECT * FROM dbo.myTable

Querying only specific columns:

SELECT "[name]", "[height]" FROM dbo.myTable

VB.NET Code - Sample 1:

Dim query As String = String.Format("SELECT {0}{1}{0}, {0}{2}{0} FROM dbo.myTable", """", "[name]", "[height]")
Dim cmdSelect As New OleDbCommand(query)

VB.NET Code - Sample 2:

Dim query As String = String.Format("SELECT {0}[name]{0}, {0}[height]{0} FROM dbo.myTable", """")
Dim cmdSelect As New OleDbCommand(query)

How can I escape square brackets in a LIKE clause?

LIKE 'WC[[]R]S123456' 

or

LIKE 'WC\[R]S123456' ESCAPE '\'

Should work.

---

Related Topics