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