From: Sundaram Ramasamy (sun@percipia.com)
Date: Fri Jun 13 2003 - 00:15:47 EDT
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
This archive was generated by hypermail 2.1.7 : Wed Apr 09 2008 - 23:26:35 EDT