perl script to search for sql statement from a file with keywords

Question

I am trying to search in a .sql file for sql statement which starts with CREATE TABLE followed by fields values then keywords [TB_DATA and TB_INDX] and ends by ;

Like

CREATE TABLE HDTB_CODE (IDPK VARCHAR(256) NOT NULL) IN TB_DATA INDEX IN TB_INDX;

.sql file statement is in multiple lines

CREATE TABLE HDTB_CODE
(
IDPK VARCHAR(256) NOT NULL
)
IN TB_DATA
INDEX IN TB_INDX;

--CREATE TABLE HDTB_BYTE
(
RTID VARCHAR(256) NOT NULL
)
IN TB_DATA
INDEX IN TB_INDX;
DROP TABLE HDTB_BYTE;
CREATE TABLE HDTB_RES
(
ARTID VARCHAR(256) NOT NULL
)
IN TB_DATA
INDEX IN TB_INDX;
CREATE TABLE HDTB_DE
(
IDPK VARCHAR(256) NOT NULL
);

-------------output----------------------

CREATE TABLE HDTB_CODE
(
IDPK VARCHAR(256) NOT NULL
)
IN TB_DATA
INDEX IN TB_INDX;

CREATE TABLE HDTB_RES
(
ARTID VARCHAR(256) NOT NULL
)
IN TB_DATA
INDEX IN TB_INDX;

cdarke · Accepted Answer · 2013-05-03 08:13:21Z

1

Try this:

use strict;
use warnings;

my $filename = 'somefile.sql';
open(my $fh, '<', $filename) || die "unable to open '$filename' for read; $!";

local $/ = "";     # Paragraph mode
while (<$fh>) {
    if (/^CREATE TABLE.+TB_DATA.+TB_INDX/s) {
        print   # or do something else
    }
}

close $fh;

The use of an empty string in $/ marks "paragraph mode", which means the record separator is two (or more) new-lines, so it breaks on the blank lines (if that is not the case, then you could use ";\n").

The s modifier after the regular expression is required for . to match new-lines.

answered May 3, 2013 at 8:13

cdarke

44.8k8 gold badges85 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vivek Over a year ago

what if there is no newline. how to use it as Paragraph mode

cdarke Over a year ago

Sorry for the delay, I have been away. If there is no newline then pick something else for the record seperator. $/ can be set to just about anything, paragraph mode is just a convienient short-cut (comes from awk).

Mattan · Accepted Answer · 2013-05-03 09:01:26Z

0

use strict;
use warnings;

$/ = '';
print grep { $_ =~ /CREATE TABLE/ && $_ =~ /INDEX/} <>;

Or:

$/ = '';
print grep { /^CREATE TABLE.+TB_DATA.+TB_INDX/s} <>;

Usage:

script_name.pl sql_file

edited May 3, 2013 at 9:01

answered May 3, 2013 at 8:07

Mattan

7538 silver badges19 bronze badges

2 Comments

cdarke Over a year ago

Not without the s modifier.

Mattan Over a year ago

It is splited into two regex, so it works. One regex will indeed need the s modifier.I added a second option though, with your nice regex :)

Collectives™ on Stack Overflow

perl script to search for sql statement from a file with keywords

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related