Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Fun with Square braces

Feb 20 2012 12:00AM by Madhivanan   

Square braces in SQL Server play a major role in T-SQL programming. When an object name contains a space, special character, etc, the only way to express them is to put them around squre braces. Consider that you want to create a table user master (with spaces between user and master), you can use [user master].

You can also wrap alias names in square braces

select 1[74]

The result is

74
-----------
1

Ok. Now I want to have [74 as column alias. What should I do? I can simply put one more opening square brace like below

select 1[[74]

which results to

[74
-----------
1

What if I want to have [74] as column alias? Simply use one more closing square brace. Isn't it? Well the following code

select 1[[74]]

throws this error

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

Ok. Now can you guess the output of the following statement?

select 1[[74]]],1[[[[[74]]]]]]]]],1[[[[74]]]]]]]

The output is

[74]        [[[[74]]]]  [[[74]]]
----------- ----------- -----------
1           1           1

Why are there more number of closing braces than the opening braces used in the statement to produce the output that has same number of opening and closing braces?

The answer is that the last sqaure brace indicates the end of the square braces wrapping. Other than the last square brace, every double closing braces is replaced as single closing brace in the output alias names. But for opening braces, the output is one less than the total number. The first opening braces indicates the beginning of the wrapping. So you need to be aware of these when using multiple square braces in the column alias.

Tags: t-sql, sql_server, sqlserver, tsql, fun, square_brace,


Madhivanan
3 · 40% · 12924
3 Readers Learned from this post
Guru Samy Learned from this post on 3/5/2012 7:02:00 AM
Profile · Blog
sunil20000 Learned from this post on 4/3/2012 1:49:00 PM
Profile
jasmyjs Learned from this post on 2/18/2013 6:09:00 AM
Profile
18
 
0
Lifesaver
 
 
3
Learned
 
0
Incorrect



Submit

10  Comments  

  • @Madhivanan: I have become a big fan of your "Fun with..." series. It all started (for me) with your "Fun With GO" and you have me hooked on to these posts since then! They are simply magical.

    Thank-you for sharing your experiences with the community!

    commented on Feb 26 2012 10:16AM
    Nakul Vachhrajani
    4 · 36% · 11635
  • Thanks for the feedback Nakul. You are welcome

    commented on Feb 27 2012 1:48AM
    Madhivanan
    3 · 40% · 12924
  • I do not post comments that much but I had no choice for this and really it was very informative

    commented on Feb 28 2012 8:48AM
    SenOvi
    443 · 0% · 90
  • Thanks SenOvi for your feedback.

    commented on Feb 28 2012 9:18AM
    Madhivanan
    3 · 40% · 12924
  • Madhivanan...

    Nice article.

    re: When an object name contains a space, special character, etc, the only way to express them is to put them aroud squre braces

    Not true. You put square braces around the special name, not the other way around.

    Also, this is not the only way to have a sql server object containing special characters. That is why you constantly see the message SET QUOTED IDENTIFIERS ON or OFF

    You can easily encapsulate an object name inside double quotes.

    So, select 74 as "74" returns the data from a column named 74 which is not a valid sql server object name. etc. etc. etc. Using quoted identifiers would be a lot easier to embed braces in object names as well.

    Cheers,

    Ben

    commented on Feb 29 2012 3:36PM
    benstaylor
    431 · 0% · 92
  • Interesting article but I must say I've never wanted to name columns to include square brackets. Our best practice is to not include anything other than alphabetic, numeric and underscores in names.

    commented on Mar 2 2012 10:11AM
    Jonathan Roberts
    76 · 2% · 745
  • Sounds complicated but will surely take not of this. Thanks for the infor!

    commented on Mar 23 2012 11:42AM
    jiennicyan
    3063 · 0% · 2
  • Hi Mr Madhivana, I wanted to put a thought over your line - When an object name contains a space, special character, etc, the only way to express them is to put them aroud squre braces.

    I think there is other way around as well - are not below two statement is same -

    SELECT 1[[74]
    SELECT 1"[74"
    

    please give your thought over this. Thanks Jeetendra

    commented on Mar 24 2012 9:05AM
    Jeetendra
    145 · 1% · 342
  • Hi Madhivanan,

    Really fantastic, am enjoying the real fun here.

    @Jeetendra: It seems both are same only, what ever given with in the double quotation will work.

    Thanks and have a great week end.

    commented on Feb 15 2013 11:17AM
    Bala Krishna
    83 · 2% · 676
  • Hi Madhivanan,

    It is an very useful tip. Since 2005 , when i began to use SMO to create applications creating databases , i am always surrounding any name of objects ( from databases thru colums,views, ,... ) with a [] pair as i faced some problems due to the use of spaces inside an object name. It may be annoying and complicating the code, but i am always prefering to have code longer to write than to have errors during the creation of a table,view,... because i have not been careful enough. I hope you will produce many other posts as good and useful like this one. Cheers...

    commented on Feb 17 2013 3:16AM
    Patrick Lambin
    167 · 1% · 296

Your Comment


Sign Up or Login to post a comment.

"Fun with Square braces" rated 5 out of 5 by 18 readers
Fun with Square braces , 5.0 out of 5 based on 18 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]