0

The following yields error:

SELECT 1 AS [dada[daa]]

Msg 105, Level 15, State 1, Line 190 Unclosed quotation mark after the character string 'dada[daa] '.

Msg 102, Level 15, State 1, Line 190 Incorrect syntax near 'dada[daa] '.

and if I have square brackets in the column alias, I can use quotes like this:

SELECT 1 AS 'dada[daa]'

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.

So, is there a way to escape these brackets somehow?

gotqn
  • 42,737
  • 46
  • 157
  • 243

2 Answers2

3

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'.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • thanks man - doubling it the last was enough - it's only for column alias, no data sources, so everything is fine. – gotqn Jun 13 '19 at 07:44
  • If you can, I do suggest `QUOTENAME` @gotqn . It's invaluable when creating dynamic SQL. – Thom A Jun 13 '19 at 07:45
2

The obvious answer would be to avoid square brackets in names. Brackets and double quotes are used as quote characters in T-SQL (double quotes are in the ANSI standard too).

Beyond that, you can doubly quote the quote character, eg :

select 1 as [da[da]]]

da[da]
------
1

Or 

select 1 as "da[da]"

da[da]
------
1

And finally

select 1 as "da""da"""

da"da"
------
1

Again, you should probably not do that, as it results in brittle code

The real problem

From the comment, it seems that the actual problem is localizing the field names for display. For some reason, this is done in the query, which can lead to various problems if the field name contains quotes or other unexpected characters.

The common solution to this is to localize the results on the presentation layer, not the query. This is supported by most reporting tools, web and desktop application stacks. Windows Forms, WPF and all ASP.NET stacks have their own localization features.

So do Reporting services although a more modern solution would pull translations from another source like a database

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • In perfect world - it will be nice :-) I am in situation where the clients are allowed to use globalization for each column alias in order to get custom text in their reports and no restrictions are set in the globalization interface ... – gotqn Jun 13 '19 at 07:54
  • @gotqn As long as the field aliases are set in the *query*, quotes will be a problem, no matter the database. A better (and more common) solution would be to localize the field names on the *UI*, eg the web page, form or reporting tool. This is supported by most web stacks and reporting tools. – Panagiotis Kanavos Jun 13 '19 at 08:51
  • @gotqn if you need to localize the aliases in the query, you'll have to check the alias to see which quote it contains and use the "other" one. Eg in MySQL you can check for ` or " and wrap the alias in the other quote. In SQL Server, it's `[]` or `"`. PostgreSQL has dollar-quoted strings. Should the alias contain *both* quotes .... don't let them enter both quotes. – Panagiotis Kanavos Jun 13 '19 at 08:56