REBOL [
    Title: "SQL As columns"
    Purpose: {Given a carefully-formatted SQL query where all selected
    columns have the "As" feature specified, extract those column names
    and return them in a block.}
]

;; [---------------------------------------------------------------------------]
;; [ It is possible to do useful things with source code if is is written      ]
;; [ with some discipline.  In this case if one has an SQL query and every     ]
;; [ column in the "select" statement has the "As" feature specified to        ]
;; [ name the column, then it should be possible to extract the column         ]
;; [ names from the SQL.  Any string after the word "As" is a column name,     ]
;; [ and the word "From" indicates the end of the column names.                ]
;; [ This function will make that scan and return the column names as          ]
;; [ a block of strings.  This function was written as part of a larger        ]
;; [ project to automate the running of SQL queries.                           ]
;; [                                                                           ]
;; [ This "markup" of the SQL must follow a particular convention.             ]
;; [ The "As" specification for a column must use the word "As" in this        ]
;; [ manner, that is, upper-case A and lower-case s.  This will distinguish    ]
;; [ the word from "AS" and "as" and let you use "AS" or "as" in the           ]
;; [ query according to your case preferences.  Similarly, the word "From"     ]
;; [ that indicates the first table to select from, must have the upper-case   ]
;; { F and the r-o-m in lower case.  This will distinguish it from a "FROM"    ]
;; [ or "from" that might be used in a sub-query.  This case-insensitivity     ]
;; [ works in SQL Server; I don't know if it works elsewhere.  We are able     ]
;; [ to make this work in REBOL because of the strict-equal? function.         ]
;; [---------------------------------------------------------------------------]

SQL-AS-COLUMNS: func [
    SQL-CMD
    /local COLNAMES WORDS LGH POS TESTWORD
] [
    WORDS: parse SQL-CMD none
    LGH: length? WORDS
    POS: 1
    COLNAMES: copy []
    while [POS < LGH] [
        if strict-equal? "From" pick WORDS POS [
            break
        ]
        either strict-equal? "As" pick WORDS POS [
            POS: POS + 1
            TESTWORD: copy trim/with pick WORDS POS "'()"
            append COLNAMES TESTWORD                       
            POS: POS + 1
        ] [
            POS: POS + 1
        ]
    ]
    return COLNAMES
]

;;Uncomment to test
;SQL-CMD: {
;select 
;cast(COLUMN1 as int) As COLUMN1
;,COLUMN2 As COLUMN2 
;,COLUMN3 As 'COLUMN3'
;,COLUMN4 As 'COLUMN4'
;,(select ITEM1 from TBL1 as T1) As ITEM  
;From TABLE1 as T1
;inner join TABLE2 AS T2
;on T1.COLUMN1 = T2.COLUMN1
;order by COLUMN1
;}
;probe SQL-AS-COLUMNS SQL-CMD
;halt