Sed or Awk to remove specific lines

Shell Programming and Scripting
1

I have searched the forum for this - forgive me if I missed a previous post.

I have the following file:

blah blah blah
blah blah blah
blah blah blah
blah blah blah
blah blah blah

alter table "informix".esc_acct add constraint (foreign key (fi_id)
references "informix".fi constraint "informix".fk_esc_acct_from_fi);
EOF

I want to use sed or awk to remove the 'alter table' statement which in this example is two lines, it can be three or even four lines. I know how to remove one line with sed but I do not know how to tell sed "remove the line with the words 'foreign key' and continue removing lines up to and including the first ';' that you run into". These lines do not always appear at the end of the file. I am using KSH on a Solaris 10 box.

Thanks for any help,

Mike

2

Try this:

 awk '/alter table/{f=1}/;$/{f=0;next}!f' file
3

Try this:

sed '/alter[^;]*;/s///g;/alter[^;]*/{N;s/alter[^;]*\n[^;]*;//g}' file

cheers,
Devaraj Takhellambam

4

I should have included a better example - sorry - here is the real file:

create table "informix".esc_acct
(
fi_id decimal(9,0) not null ,
ea_nbr char(14) not null ,
rc_svcg_st_cd char(2) not null ,
ea_type_nm char(8) not null ,
ea_closd_dt date,
primary key (fi_id,ea_nbr) constraint "informix".pk_esc_acct
) extent size 12 next size 8 lock mode page ;
revoke all on "informix".esc_acct from "public" as "informix";

alter table "informix".esc_acct add constraint (foreign key (fi_id)
references "informix".fi constraint "informix".fk_esc_acct_from_fi);

When I ran Franklin's script I got:

nawk '/alter table/{f=1}/;$/{f=0;next}!f' esc_acct_resize.sql

{ TABLE "informix".esc_acct row size = 34 number of columns = 5 index size = 36 }
create table "informix".esc_acct
(
fi_id decimal(9,0) not null ,
ea_nbr char(14) not null ,
rc_svcg_st_cd char(2) not null ,
ea_type_nm char(8) not null ,
ea_closd_dt date,
primary key (fi_id,ea_nbr) constraint "informix".pk_esc_acct

So it trunc'd off too much... I am messing with the cmds that Franklin sent me.

Thanks,

Mike

5

Ok, I have adjust the code, I didn't expect other lines with a semicolon at the end:

awk '/alter table/{f=1} /;$/ && f{f=0;next} !f'  file
6

Beautiful Franklin - works very nicely. I will now set about deconstructing the lines to figure them out for future application.

MUCH APPRECIATED!!!!

Mike