How to handle extra Square brackets in SQL column name

Question

I need to pass Column name as [ID-ColumnName] as column name in order by for a table.

Ex:

Select [ID-ColumnName],[ID2-ColumnName2] FROM TABLE1 ORDER BY [ID-ColumnName]

Problem is if the ColumnName which is dynamic name comes with one opening/closing square brackets e.g Column[Name or Column]Name

In this scenario the SQL statement is breaking; Problem is I dont know how many such open/close square brackets can come.

Wrapping the column name with square brackets is mandatory in my scenario.

Please help me handling this scenario.

Look here http://stackoverflow.com/questions/10454597/how-do-i-query-column-names-that-contain-square-brackets — ventik, Apr 20 '17 at 06:50
Are you looking for [`QUOTENAME`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql)? — Damien_The_Unbeliever, Apr 20 '17 at 06:50
@Damien_The_Unbeliever: I had tried that but the in some of my scenarios the SQL statement QUOTENAME('SQL') works and in some scenarios it required to be given as QUOTENAME(SQL) and hence I am not able to take advantage of this as the QUOTENAME() will be passed from Dot Net code — Abhinaw Kaushik, Apr 20 '17 at 06:59

score 2 · Answer 1 · answered Apr 20 '17 at 09:21

All,

I was able to solve the problem like given below:

Create a table having square brackets in name (You can choose to have any combination of open/close brackets or single open/close).

CREATE TABLE [dbo].[TestTable](
    [OF-[Test [Name]]]]] [nvarchar](100) NULL,
    [Name] [nvarchar](100) NULL
) ON [PRIMARY]

GO

And the Prepare the Dynamic Query and use QUOTENAME() function as suggested by @Damien_The_Unbeliever; but make sure the QUOTENAME() function should not be given as string; post that execute the Query.

DECLARE @orderBy NVARCHAR(MAX),@sql NVARCHAR(MAX)

SET @orderBy = N' ORDER BY '+QUOTENAME('OF-[Test [Name]]')+' ASC'
SET @sql=N'select '+QUOTENAME('OF-[Test [Name]]')+' from TestTable'
--PRINT(@sql +@orderBy)
EXEC(@sql +@orderBy)

Thanks for all your help & suggestions.

How to handle extra Square brackets in SQL column name

1 Answers1