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

[Gordon Burditt]

> 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]
`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.

If a table or field name has wierd characters in it, enclose it in
backquotes. I'm not real sure how to deal with the situation where
a table name has both backslashes and backquotes in its name, but
hopefully that will never come up.

Gordon L. Burditt

[Bill Karwin]

"Usulnet" <gd**@sbcglobal.net> wrote in message
news:JR*******************@newssvr21.news.prodigy. com...

I'm working with a poorly writtend database that has some
fields named as such: MTIC_PROD_VEND[ 1]

You need to use "delimited identifiers". This is a SQL feature that allows
us to use SQL reserved words and special characters in the names of tables
and fields.

MySQL uses backticks by default to delimit identifiers:

SELECT `MTIC_PROD_VEND[ 1]` FROM . . .

The ANSI SQL standard uses double-quotes to delimit identifers:
MySQL supports this too, if you set the ANSI_QUOTES SQL mode.
See http://dev.mysql.com/doc/refman/5.0/...-sql-mode.html

SET GLOBAL SQL_MODE = 'ANSI_QUOTES';
SELECT "MTIC_PROD_VEND[ 1]" FROM . . .

Microsoft SQL Server uses square brackets to delimit identifiers.
See http://msdn2.microsoft.com/en-US/library/ms176027.aspx

Regards,
Bill K.