Can some one help me in getting the table names from sql's e.g select d.acc_no, d.mon , case when prev.balance < 1 then U when prev.balance > 1 then L else X end bal_rate, opening_balance from cust_bill cb join cust_bill_2 d on cb.acc_no = d.acc_no and cb.mon = d.mon left outer join cust_bill prev on prev.acc_no = d.acc_no Then table names are cust_bill, cust_bill_2 , cust_bill corresponding aliases are cb, d and prev Can this be done in unix?. Since the table names and aliases are wrapping each other i find it more difficult to parse it. Like this I have many sqls.

This nawk script searches for the "from" and "join" keywords and decides whether the table name is on the same line or the next line. This breaks if you have the "join" or "from" keyword in a comment: #!/bin/ksh nawk ' BEGIN { nfrom=0; } { if(NF == 0) # skip blank lines continue if(nfrom == 1) { # print the table name form the next line printf("%s\n", $1) nfrom=0 continue } if( $1 ~ /from/) { if(NF > 1) # table name after from keyword printf("%s\n", $2) else # table name on next line, get it nfrom=1 continue } # if join ends the string get the next line if( $1 ~ /join$/) { nfrom=1 continue } # join does not start the line if( $0 ~ /join/) # table name after join keyword, table name second field from end printf("%s\n", $(NF-1) ) } ' sqlfile.txt

Thanks nails..It works well. some exceptions are there e.g the last 2 lines may be in a single line. i.e left outer join cust_bill prev on prev.acc_no = d.acc_no in this case it picks up the "=" sign. So now the expectation is if from/join is $1, then $2 is my table name ( ie NF > 1) if from/join is $NF, then $1 in next line is my table name if from/join is anywhere in the line, then the next word will be my table name. How to search for the next word?

Wow! you are making my life more difficut <grin> In the case where the "join" keyword doesn't end the string, change the algorithm finding the field number of the "join" keyword. Grab the next field which will be the table name: #!/bin/ksh nawk ' BEGIN { nfrom=0; } { if(NF == 0) # skip blank lines continue if(nfrom == 1) { printf("%s\n", $1) nfrom=0 continue } if( $1 ~ /from/) { if(NF > 1) # table name after from keyword printf("%s\n", $2) else # table name on next line, get it nfrom=1 continue } # if join ends the string get the next line if( $1 ~ /join$/) { nfrom=1 continue } if( $0 ~ /join/) { # find the field that contains join # grab the next field for(i=1; i<= NF; i++) if($i == "join") printf("%s\n", $(i+1) ) } } ' sqlfile.txt

hi nails..thanks a lot.. your way of solving things is different from others.. its unique and much appreciated !!!.

arulactive: Thank you for the kind words; it's much appreciated. Someday I will know awk as good as Ghostdog. :) Regards, Nails