CODEWITHSUNDEEP
perl
Vivek
Vivek

Reputation: 39

perl script to search for multiple lines from a file with start and end keywords

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 ; it in multiple lines

.sql file statement is in multiple lines

-- CREATE TABLE HDTB_COD;
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;
-- DROP TABLE HDTB_COD;
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;

Upvotes: 0

Views: 292

Answers (2)

FMc
FMc

Reputation: 42421

Here is an example of how to create quick-and-dirty parsing pipelines. Once you understand the basic pattern, it's easy to add more filtering steps (with grep) or transforming steps (with map)

# Slurp entire file.
my $sql = do { local $/ = undef; <> };

# 1. Grab the CREATE TABLE statements.
# 2. Retain only the statements of interest.
# 3. Modify the statements as needed before printing.
print
    map  { "$_\n" }                         # 3b. Add trailing newlines.
    map  { s/\s+/ /g; $_ }                  # 3a. Normalize whitespace.
    grep { /IN TB_INDX/ }                   # 2b. Filter.
    grep { /IN TB_DATA INDEX/ }             # 2a. Filter.
    $sql =~ /^(CREATE TABLE .+?;)\s*$/gsm;  # 1.  Grab.

Upvotes: 0

Vorsprung
Vorsprung

Reputation: 34377

perl -n -e 'chomp; next if (/^--/);@p=() if /CREATE TABLE/; push @p,$_; if (/IN TB_DATA INDEX IN TB_INDX;/) { print "@p\n"; }' t.sql

How it works 

chomp; # remove newlines
next if (/^--/);  #skip lines that are SQL comments
@p = () if /CREATE TABLE/; #start of a table definition, clear array @p
push @p, $_; # put current line into array @p
#condition found, print @p    
if (/IN TB_DATA INDEX IN TB_INDX;/) { print "@p\n"; }

Upvotes: 1

Related Questions