1

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

1
  • 2
    Quite the challenge, to parse semi-arbitrary SQL! (ref: the substr() call)
    – Jeff Schaller
    Apr 16, 2018 at 10:59

2 Answers 2

0

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.

0

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.

2
  • Could you pls explain about the first command
    – Balaji
    Apr 16, 2018 at 13:06
  • @Balaji, see edit. Apr 16, 2018 at 13:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .