Querying a column with brackets in its name

[Usulnet]

Hi All,
I was hoping someone has experienced this before, I'm having trouble
googling this. I'm working with a poorly writtend database that has some
fields named as such: MTIC_PROD_VEND[ 1]

I do not have the option to rename this field but I do need to retreive data
from it with SQL. I've tried a suggestion of [MTIC_PROD_VEND[ 1]]] and that
doesn't seem to work, it may be because the 1 has a space leading it. Does
anyone happen to have any suggestion to try to work around this? Any
assistance would be greatly appreciated. Thanks.

Regards,
Stephan

[Stu]

Well, if you used the example as you typed it, then you have a typo;
there's an extra bracket at the end: [MTIC_PROD_VEND[ 1]]] should be
[MTIC_PROD_VEND[ 1]].

Of course, if it still doesn;t work, you might try using standard
quotation marks, e.g.:
"MTIC_PROD_VEND[ 1]"

HTH,
Stu

[Erland Sommarskog]

Usulnet (gd**@sbcglobal.net) writes:

I was hoping someone has experienced this before, I'm having trouble
googling this. I'm working with a poorly writtend database that has some
fields named as such: MTIC_PROD_VEND[ 1]

I do not have the option to rename this field but I do need to retreive
data from it with SQL. I've tried a suggestion of [MTIC_PROD_VEND[ 1]]]
and that doesn't seem to work, it may be because the 1 has a space
leading it. Does anyone happen to have any suggestion to try to work
around this? Any assistance would be greatly appreciated. Thanks.

[MTIC_PROD_VEND[ 1]]] should indeed be what you need; you need to double
the closing delimiter to include in the string. What exactly do you mean
with "doesn't seem to work"?

Stu's suggestioned to use "" certatinly looks cleaner anyway.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Stu]

Obviously, I don''t work with brackets in my table names; just curious,
why the double "]" at the end? I just tried it, and it blew up. I'd
like to know so that when I encounter it in the future, I'll understand
what's going on.

Stu

[Erland Sommarskog]

Stu (st**************@gmail.com) writes:

Obviously, I don''t work with brackets in my table names; just curious,
why the double "]" at the end? I just tried it, and it blew up. I'd
like to know so that when I encounter it in the future, I'll understand
what's going on.

Because in T-SQL whenever you need to include the closing delimiter in
whatever you are delimiting, you need double it:

PRINT 'It''s one o''clock, and time for lunch'
SELECT * FROM [My[]]brackets]
SELECT "Double""quote" = '"'

There is a built in function that can help you with this: quotename().

SELECT quotename('MTIC_PROD_VEND[ 1]')
SELECT quotename(@myinput, '''')

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx