UNIX script to convert queries

Question

I need a UNIX shell script to convert my queries from Java compatible to Oracle compatible format. ie.

I have all the java compatible queries:

java:

SELECT a, b, c, d, e, f,g "+//    "from test where year(timestamp)=year(today) and month(timestamp)=month(today) " 
+//    "and day(timestamp)=2 and h='" + "W" + "'"

Oracle

SELECT a, b, c,d,e,f,g from test where year(timestamp)=year(today) and month(timestamp)=month(today) and day(timestamp)=2 and h='W'

Is it possible using sed or awk?

Answer 1

sed '/SELECT/s/"[^"]\+"//g' <FILE>

Might help, but only if the query has balanced "s on the same line. E.g. it fails for:

SELECT a, b, c, d, e, f,g "+//    
"from test where day(timestamp)=2 and h='" + "W" + "'

HTH

Answer 2

First of all, keep all your text in a single line.

You can do it with one-line Perl code:

perl -n -E 's/"[^"]+"//mg;s/"$//;print' java-sql.txt >oracle-sql.txt

It will remove the tail `"' also. If you don't have a file, the pipe is also working here

you-code-print-java-sql-to-stdout.exe | perl -n -E 's/"[^"]+"//g;s/"$//;print' 

You will get the oracle version from the stdout of Perl.