SQL SELECT cannot reference “INDEX” column

Using Advantage SQL, I have the following query:

SELECT TOP 10 mytable.*
FROM "mytable.ADT" mytable
ORDER BY date DESC

This returns this data set:

INDEX            NR     NAME       Date
---------------------------------------------------
"145443"         115    Bob        19.03.2021 12:26
"23545",1        215    Steve      19.03.2021 12:09
"564543","",0    215    John       19.03.2021 12:09
"456234"         215    Mark       19.03.2021 12:09

What I want to do is work with the data in the INDEX column. But if I run a normal SELECT query with this field name:

SELECT mytable.INDEX etc

it doesn’t run. I also cannot add it as an alias or anything.

Are there any workarounds to pull the specific column into a SELECT? My goal is to do text manipulation to remove the quote marks and extract only the pure number that sits in the middle.

This is the first time I’ve had this issue – I’m guessing the word INDEX is somehow a function name which screws things up. But I also am guessing there is a way around it?

Thanks.

Answer

INDEX is a SQL keyword — and probably reserved, which is the problem.

You need to escape it. I believe Advantage supports both double quotes and square braces:

SELECT mytable."INDEX", mytable.[INDEX]
FROM "mytable.ADT" mytable
order by date desc

Note that capitalization is important in some databases (but not Advantage SQL) when you escape column names.