Extracting lines based on identifiers into multiple files respectively

Shell Programming and Scripting
1

consider the following is the contents of the file

cat 11.sql
drop procedure if exists hoop1 ;
Delimiter $$
CREATE PROCEDURE hoop1(id int)
BEGIN
END
$$
Delimiter ;
.
.
.
.
drop procedure if exists hoop2;
Delimiter $$
CREATE PROCEDURE hoop2(id int)
BEGIN
END
$$
delimiter ;
.
.
DROP VIEW IF EXISTS view1;
 CREATE VIEW view1
 AS
 SELECT blah blah  FROM blah etc etc
 WHERE blah blah
 GROUP BY  id;
.
.
.
DROP VIEW IF EXISTS view2;
 CREATE VIEW view2
 AS
 SELECT blah blah  FROM blah etc etc
 WHERE blah blah
 GROUP BY  id;
.
.
.
drop procedure if exists hoop3;
Delimiter $$
CREATE PROCEDURE hoop2(id int)
BEGIN
END
$$
Delimiter ;

i tried

perl -lne '(/^\s*CREATE\s+PROCEDURE/i .. /^\s*\)\s+Delimiter/i) && print;' "11.sql" > onlyproc1.sql
 
perl -lne '(/^\s*CREATE\s+VIEW/i .. /^\s*\)\s+;/i) && print;' "11.sql" > onlyviews1.sql

its not working properly.. i want the all the procedures from CREATE PROCEDURES to Delimiter ( or it can be small case delimiter) in one file and also all views from CREATE VIEW to ";" which marks the end of that particular view in another file.... how to do this.. :-/ plz help :wall:

2 
$ nawk '/CREATE PROCEDURE/,/[dD]elimiter/' infile > create_procedure_outfile

$ nawk '/CREATE VIEW /,/;/' infile > create_view_outfile
1 Like
3

i am getting the error
-bash: nawk: command not found
what to do?

4

Use awk or gawk (which may well be the same anyway, if you're running a Linux OS).

1 Like
5 
echo "`sed -n "/$firstword/,/;/ w viewextract.sql" < onlyviews1.sql`"

where firstword is CREATE VIEW some view name.. similarly procedure name....
now my doubt is consider i have two procedures of same name.. so when i execute this

 echo "`sed -n "/$firstword/,/[dD]elimiter/ w extractproc.sql" < onlyproc1.sql`"

and if

cat onlyproc1.sql
CREATE PROCEDURE ABC(id int)
BEGIN
END
$$
Delimiter ;
CREATE PROCEDURE xyz(id int)
BEGIN
END
$$
Delimiter ;
CREATE PROCEDURE ABC(id int)
BEGIN
END
.
.
.etc

the sed command will write two procedures to the file extractproc.sql which are repeated.. is there any way to make sure the command takes only one procedure and writes it to the file...?

6

Then expand your firstword pattern accordingly ..

7

consider the onlyproc1.sql file which i mentioned above...
it has two procedures under the name ABC..
so if i use

firstword="CREATE PROCEDURE ABC"
echo "`sed -n "/$firstword/,/[dD]elimiter/ w extractproc.sql" < onlyproc1.sql`"

then extractproc.sql will have two procedures of same name... but i was the fle to have only one procedure...is there any way we could do it in one line... i could ofcourse do it using while loop by writing to another file till first procedure ends but its time consuming which leads to code inefficiency...

8

Just supress the output .. :wink:

$ sed -n '/CREATE PROCEDURE ABC/,/[dD]elimiter/p' infile | nawk '!x[$1]++'
1 Like
9

thanks jayan... your code is working.. :slight_smile: