I have a file with some SQLs and I want to find out the fields that have cast by using shell scripting.
For example, I have a file abc.txt with the below sql
SELECT field1,field2,field3,field4,cast(field5 as integer),cast(substr(field6,5,10) as integer),(case when field7 = '0000/00/00' then cast(field7 as date) else (field8 as date) end) as field7, substr(field9,5,10) FROM TEMP;
Desired Output:
field5
field6
field7
How about the following:
awk 'BEGIN{FS="cast\\(";OFS="\n\n"}{ for(i = 1; i <= NF; i++) { sub("[ ),].*","",$i);gsub("^.*\\(","",$i) } {$1=""; print}}'
This solution iterates over instances of "cast(" on any line, then strips out prefixes and suffixes.
To look for field<decimals> occurrences inside cast(...) statements, assuming there are no mismatched parenthesis, with GNU grep or compatible built with PCRE support:
<abc.txt grep -Po 'cast(\((?:[^()]++|(?1))*\))' |
grep -Po '\bfield\d+\b'
That's using PCRE's ability to define recursive regular expressions. Above (?1) refers to the regular expression enclosed in (...), so we're looking for cast followed by a regexp "R" that starts with ( followed by any number of either non-parens (++ is just the non-backtracking version of +) or more "R"s followed by ).
That allows us to find the matching ) for the opening ( that follows cast.
The second grep only extracts the field<decimal> (surrounded by word boundaries (\b)) from those cast(...) statements that the first grep extracts.
That assumes those SQL statements are on a single line. If not, you can add the -z option to the first grep.
