How do I request column names containing square brackets?

Question

I have a csv which I need to parse in a gridview in vb.net. If I do SELECT *, I will get the data without any problems. However, I am in a situation where I need to refer to the actual column names. And the problem is that I do not control the application that generates csv, and they make the column names enclosed in square brackets.

How can I do something like this:

Dim cmdSelect As New OleDbCommand(SELECT "[name], [height] FROM myTable") 

so i get a data refund?

So, to be absolutely clear: I have an application that creates csv with column headings [name] and [height] in a table named myTable, and for life I can’t figure out how to return [name] and [height] in particular.

Answer 1

If column names have square brackets, you can use double quotes to wrap column names. The following sample has been tested in SQL Server.

Script:

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

Query all columns:

 SELECT * FROM dbo.myTable 

Query only specific columns:

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

VB.NET Code Example 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) 

Answer 2

I assume that this is for SQL Server.

Objects containing the symbol] should be indicated with [].

In the following table:

 CREATE TABLE [t]]] ( [i]]d] int primary key, [da]]]ta] int ) 

You can select it using:

 SELECT [da]]ta], [i]]d] FROM [t]]] 

If you are looking for a way to use T-SQL to quote object names, you can use QUOTENAME.

 SELECT QUOTENAME('t]') 

In SQL Server, you can also use double quotes to quote object names; however, there are two things to check.

First you need to make sure you enable QUOTED_IDENTIFIER. Some drivers will be for you automatically.

 SET QUOTED_IDENTIFIER ON 

The second thing you need to do is make sure that you avoid any other double quotes in the name of your object. From T-SQL, QUOTENAME reappears.

 SELECT QUOTENAME('t"]') "t""]" 

In VB.Net, you want to do something like:

 dim queryStr As String = String.Format("SELECT ""{1}"" FROM dbo.myTable", String.Replace(col1, """", """"""))