Shell Programming and Scripting

Hi,

I have a file which contains the following :

select * from test where test_id=1;

select id
from test1, test2 where test_id=1 and test_id=2;

select * from
test1, test2, test3 where test_id=4 and test2_id where in (select test2_id from test2);

select
id1, id2 from test
where test_id=1;

............................

I need all words between first "from" (including) and first "where" condition (excluding) to be deleted irrespective of whether they span on multiple lines ...

ie., the required output is :

select * where test_id=1;
select id where test_id=1 and test_id=2;
select * where test_id=4 and test2_id=6 where in (select test2_id from test2);
select id1, id2 where test_id=1;


Thanx

-RR

No doubt there is a better way but this sure seemed to work for me:

Code:

nawk '
    BEGIN{RS=";"}
    {
        gsub(/[Ff][Rr][Oo][Mm].*[Ww][Hh][Ee][Rr][Ee]/,"where",$0)
        gsub(/\012/,"",$0)
        printf("%s;\n",$0)
    }' yourfilename

My only issue is that there is an extract semicolon at the end of2the output.

Code:

select * where test_id=1;
select id where test_id=1 and test_id=2;
select * where in (select test2_id from test2);
select id1, id2 where test_id=1;
;

Thomas

Thanx a lot ... yeah it does work ... but on some statements like these :

select * from test WHERE test_id in (select test_id1 from test1 where test_id1=1);

the output is :

select * where test_id1=1);

I need it as :

select * WHERE test_id in (select test_id1 from test1 where test_id1=1);

ie., it needs to cut at the first where condition ... is there a way to specify this ?

Thanx

-RR

Sorry, my goof. Change the first gsub to sub.

Code:

nawk '
    BEGIN{RS=";"}
    {
        sub(/[Ff][Rr][Oo][Mm].*[Ww][Hh][Ee][Rr][Ee]/,"where",$0)
        gsub(/\012/,"",$0)
        printf("%s;\n",$0)
    }' yourfilename

New output:

Code:

select * where test_id=1;
select id where test_id=1 and test_id=2;
select * where test_id=4 and test2_id=6 where in (select test2_id from test2);
select id1, id2 where test_id=1;
;

Hmm ... I still don't seem to be getting the required output ...

nawk 'BEGIN{RS=";"}{sub(/[Ff][Rr][Oo][Mm].*[Ww][Hh][Ee][Rr][Ee]/,"where",$0);gsub(/\012/,"",$0);printf("%s;\n",$0)}' test.sql

Output :

select * where test_id=1);

cat test.sql

select * from test WHERE test_id in (select * from test1 where test_id=1);

Required output :

select * where test_id in (select * from test1 where test_id=1);

Words between first "from" and first "where" condtion (case insensitive, exact match)
need to be removed ...

Thanx

-RJ

Problem is that the regular expression /[Ff][Rr][Oo][Mm].*[Ww][Hh][Ee][Rr][Ee]/ will match the largest matching pattern, i.e. from the first "from" to the last "where".

You could try using /[Ff][Rr][Oo][Mm][^\(]*[Ww][Hh][Ee][Rr][Ee]/ which works on the example given but may not work in all cases.

Good catch; I hadn't got back to see were my error was. I also agree that there are likely to be cases that tis script doesn't with.