Parse a SQL select statement

[james zhou]

Hi, in the project I am currently working on I need to parse a SQL
SELECT statement so that I can get all the selected elements in the
query. For example I need to parse the following stmt:
Select SUBSTR(ACCT_USAGE_DETAIL_GUIDE_VIEW.C1,1,60) AS KEY,
ACCT_USAGE_DETAIL_GUIDE_VIEW.BEGINTS,
TO_CHAR(TO_DATE(BEGINTS, 'YYYYMMDDHH24MISS') - 30,
'YYYYMMDDHH24MISS') AS XBEGINTS,
ACCT_USAGE_DETAIL_GUIDE_VIEW.CCS_ACCOUNT,
ACCT_USAGE_DETAIL_GUIDE_VIEW.REPORTING_NUMBER,
ACCT_USAGE_DETAIL_GUIDE_VIEW.ENDTS,
DECODE(ENDTS, '99999999999999', '99999999999999',
TO_CHAR(TO_DATE(ENDTS, 'YYYYMMDDHH24MISS') + 30, 'YYYYMMDDHH24MISS'))
AS XENDTS,
ACCT_USAGE_DETAIL_GUIDE_VIEW.SYMBOLIC_CATALOG_ENTRY
from ACCT_USAGE_DETAIL_GUIDE_VIEW
where length(originating_number) = 10;

In the above example, "SUBSTR(ACCT_USAGE_DETAIL_GUIDE_VIEW.C1,1,60) AS
KEY" is the first element, and "ACCT_USAGE_DETAIL_GUIDE_VIEW.BEGINTS"
is the second element, so on and so forth. Apparently I cant use comma
as delimiter to break down the elements. Can anyone shed some lights
on this?

Thanks,

 

[Lee Fesperman]

Hi, in the project I am currently working on I need to parse a SQL
SELECT statement so that I can get all the selected elements in the
query. For example I need to parse the following stmt:

...

In the above example, "SUBSTR(ACCT_USAGE_DETAIL_GUIDE_VIEW.C1,1,60) AS
KEY" is the first element, and "ACCT_USAGE_DETAIL_GUIDE_VIEW.BEGINTS"
is the second element, so on and so forth. Apparently I cant use comma
as delimiter to break down the elements. Can anyone shed some lights
on this?

No problem. Elements in the list are separated by commas which are not enclosed in
parentheses or quotes (single or double). This syntax is common to many languages.