SUMMARY Re: multi line match regular expression

I got lots of response thanks for every one of you.

Here is the answers.

1.

#!/usr/bin/perl

my @data = qx[cat dbdump];
my $vals = join(' ', @data);

$vals =~ s/\n//g;
$vals =~ s/\s+/ /g;

my @line = split(';', $vals);

my @create = grep { /CREATE/i } @line;
my @insert = grep { /INSERT/i } @line;

print "\n\n-CREATE-\n\n";
print "$_;\n" for @create;

print "\n\n-INSERT-\n\n";
print "$_;\n" for @insert;


2.

 BEGIN {flag = 0}
  /INSERT/ {flag = 1}
  flag == 1 {print $0}
  /;/ {flag = 0}

Save the above lines as a file, like "insert.awk", then just run:

  awk -f insert.awk text_file_name > insert_statements_only


3.

 awk 'BEGIN{RS=";"} /INSERT/{print;next} /CREATE/{c=c $0} END {print c}'

-SR

----- Original Message -----
From: "Sundaram Ramasamy" <sun@percipia.com>
To: "Sun Managers" <sunmanagers@sunmanagers.org>
Sent: Tuesday, June 10, 2003 2:23 AM
Subject: multi line match regular expression


> Hi,
>
> I have the following SQL statements in a text file. I want to extract all
> insert SQL statements fist, then I want to extract all create SQL
statement.
>
> Can some one help me with sed or perl regular expression for this?
>
> Thanks
> -SR
>
> CREATE TABLE if not exists ptable (
>     id    VARCHAR(60),
>     name varchar(100),
>     PRIMARY KEY ( id ),
>     UNIQUE INDEX premiseidx ( id ) );
> insert into ptable (id)
> values( '660233992481342shujmbodgVNY34811731p4CE10IIMp06CKOWags',
> 'Columbus');
> CREATE TABLE if not exists userinfotable (
>         userid          VARCHAR(20)     PRIMARY KEY,
>         password        VARCHAR(20)     NOT NULL,
>         lname           VARCHAR(30) NOT NULL DEFAULT "",
>         fname           VARCHAR(30) NOT NULL DEFAULT "",
>         priv        INT(5)      NOT NULL DEFAULT 0
>         );
> INSERT INTO userinfotable VALUES ('aa',  'c3lzMTIz', 'aa',  'sys',  3);
> INSERT INTO userinfotable VALUES ('ddd', 'ZGVtbw==', 'ddd', 'demo', 3);
> CREATE TABLE if not exists roomhistorytable(
>     id                 INTEGER DEFAULT 1 auto_increment,
>     roomno             VARCHAR( 5 ) NOT NULL,
>     transtype          INTEGER  NOT NULL,
>     transtime          DATETIME     NOT NULL,
>     transsource        INTEGER,
>     transsubsource     VARCHAR(25),
>     transparams        VARCHAR(225),
>     transresult        INTEGER,
>     transresultdetails VARCHAR(225),
>     PRIMARY KEY (id),
>     INDEX roomhistoryidx(id) );
_______________________________________________
sunmanagers mailing list
sunmanagers@sunmanagers.org
http://www.sunmanagers.org/mailman/listinfo/sunmanagers