Querying a column with brackets in its name

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

Re: Querying a column with brackets in its name

"Usulnet" wrote in message
news:JR5Sf.60848$dW3.50653@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/en/server-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.

Re: Querying a column with brackets in its name

> 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