INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

need help with a regular expression

need help with a regular expression

(OP)
*** I didn't realize that this was a dead forum. I will ask my questions elsewhere ***


I am using the following match to search a sql script

if $ScanString =~ m/insert\s.+doc.+[\(|values|select|;]?/is;

It is returning true on the following code fragment. What I want is that if the table name of DOC follows the INSERT, but if it gets to any of "(", SELECT or ";" it will return false. In other words if the DOC is not there before I hit "(", SELECT or ";" it will always return false. The perl match from above is returning a false positive because the string "DOC_NUM" is in the attached select.

Any help would be appreciated.

The script fragment follows

INSERT INTO gl_trn
( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES,
REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT,
CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM,
POST_ID_NUM, BNK_CO_CD)
SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'),
'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18),
decode(dc_cd, 'C', 'D',
'D', 'C'),
amt, TRUNC(SYSDATE), gl_trn_origin_cd,
NULL, co_unit, floor((sysdate-trunc(sysdate))*86400),
'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD,
DOC_NUM, POST_ID_NUM , BNK_CO_CD
FROM gl_trn
WHERE post_dt = to_date('&from_post_dt', 'MMDDRR')
AND jrnl_cd = 'A'
AND co_cd = 'RAY'
AND NOT EXISTS (SELECT 'X'
FROM gl_reversed g
WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num);

Bill
Lead Application Developer
New York State, USA

RE: need help with a regular expression

No matter what forum you try regular expressions are probably not going to do what you want in a single pass. You will need to parse the document with a script that locates the first instance of the word THEN starts parsing from that point looking for the next instance of whatever word you use as the end point.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

RE: need help with a regular expression

for me this regex seems to work:

CODE

/insert\s+into\s+\S*DOC\S*[ (]/i 

RE: need help with a regular expression

Here is an example
beilstwh.pl

CODE

#!/usr/bin/perl
use strict;
use warnings;

# change record separator to SQL Statements seperator - i.e.: ";"
local $/ = ";";

my $insert_table_name = "";

while (my $sql_stmt=<DATA>) {
    chomp($sql_stmt);
    $sql_stmt =~ s/\n/ /g;
    next if ($sql_stmt =~ /^\s*$/);

    print "=== SQL Stalement #$.:\n";
    print "$sql_stmt\n\n";

    if ($sql_stmt =~ /insert\s+into\s+\S*DOC\S*[ (]/i) {
      print "\t\t*** MATCHED !!! ***\n";
    }
}

__DATA__
INSERT INTO gl_trn
( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES,
REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT,
CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM,
POST_ID_NUM, BNK_CO_CD)
SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'),
'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18),
decode(dc_cd, 'C', 'D',
'D', 'C'),
amt, TRUNC(SYSDATE), gl_trn_origin_cd,
NULL, co_unit, floor((sysdate-trunc(sysdate))*86400),
'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD,
DOC_NUM, POST_ID_NUM , BNK_CO_CD
FROM gl_trn
WHERE post_dt = to_date('&from_post_dt', 'MMDDRR')
AND jrnl_cd = 'A'
AND co_cd = 'RAY'
AND NOT EXISTS (SELECT 'X'
FROM gl_reversed g
WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num);

insert into MYDOCTAB(VAR1, VAR2, VAR3)
   values ('foo','bar','baz')
;
insert into MYTABLE
  select * from DOCTAB t
where t.name = 'MYDOC'
;
INSERT INTO gl_trn_DOC
( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES,
REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT,
CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM,
POST_ID_NUM, BNK_CO_CD)
SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'),
'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18),
decode(dc_cd, 'C', 'D',
'D', 'C'),
amt, TRUNC(SYSDATE), gl_trn_origin_cd,
NULL, co_unit, floor((sysdate-trunc(sysdate))*86400),
'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD,
DOC_NUM, POST_ID_NUM , BNK_CO_CD
FROM gl_trn
WHERE post_dt = to_date('&from_post_dt', 'MMDDRR')
AND jrnl_cd = 'A'
AND co_cd = 'RAY'
AND NOT EXISTS (SELECT 'X'
FROM gl_reversed g
WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num);
select * from doc; 

Output:

CODE

mikrom@mikrom-Lenovo-S500 ~/Work $ perl beilstwh.pl
=== SQL Stalement #1:
INSERT INTO gl_trn ( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES, REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT, CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM, BNK_CO_CD) SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'), 'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18), decode(dc_cd, 'C', 'D', 'D', 'C'), amt, TRUNC(SYSDATE), gl_trn_origin_cd, NULL, co_unit, floor((sysdate-trunc(sysdate))*86400), 'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM , BNK_CO_CD FROM gl_trn WHERE post_dt = to_date('&from_post_dt', 'MMDDRR') AND jrnl_cd = 'A' AND co_cd = 'RAY' AND NOT EXISTS (SELECT 'X' FROM gl_reversed g WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num)

=== SQL Stalement #2:
  insert into MYDOCTAB(VAR1, VAR2, VAR3)    values ('foo','bar','baz') 

		*** MATCHED !!! ***
=== SQL Stalement #3:
 insert into MYTABLE   select * from DOCTAB t where t.name = 'MYDOC' 

=== SQL Stalement #4:
 INSERT INTO gl_trn_DOC ( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES, REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT, CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM, BNK_CO_CD) SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'), 'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18), decode(dc_cd, 'C', 'D', 'D', 'C'), amt, TRUNC(SYSDATE), gl_trn_origin_cd, NULL, co_unit, floor((sysdate-trunc(sysdate))*86400), 'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM , BNK_CO_CD FROM gl_trn WHERE post_dt = to_date('&from_post_dt', 'MMDDRR') AND jrnl_cd = 'A' AND co_cd = 'RAY' AND NOT EXISTS (SELECT 'X' FROM gl_reversed g WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num)

		*** MATCHED !!! ***
=== SQL Stalement #5:
 select * from doc 

RE: need help with a regular expression

Other approach would be to extract table name from SQL-INSERT-statement and check if it contains the desired pattern.

I tried this:
beilstwh_02.pl

CODE

#!/usr/bin/perl
use strict;
use warnings;

# change record separator to SQL Statements seperator - i.e.: ";"
local $/ = ";";

my $insert_table_name = "";

while (my $sql_stmt=<DATA>) {
    chomp($sql_stmt);
    $sql_stmt =~ s/\n/ /g;
    next if ($sql_stmt =~ /^\s*$/);

    print "=== SQL Stalement #$.:\n";
    print "$sql_stmt\n\n";

    # if it's INSERT statement then extract table name
    if ($sql_stmt =~ /insert\s+into\s+(\w+)[ (]/i) {
       my $insert_table_name = uc($1);
       print "\t\t*** INSERT into table: '$insert_table_name'\n";
       # look at table name 
       if  ($insert_table_name =~ /DOC/i) {
         print "\t\t*** MATCHED !!! ***\n";       
       }
    }
    else {
       print "\t\t*** not an INSERT statement !\n";
    } 
}

__DATA__
INSERT INTO gl_trn
( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES,
REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT,
CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM,
POST_ID_NUM, BNK_CO_CD)
SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'),
'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18),
decode(dc_cd, 'C', 'D',
'D', 'C'),
amt, TRUNC(SYSDATE), gl_trn_origin_cd,
NULL, co_unit, floor((sysdate-trunc(sysdate))*86400),
'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD,
DOC_NUM, POST_ID_NUM , BNK_CO_CD
FROM gl_trn
WHERE post_dt = to_date('&from_post_dt', 'MMDDRR')
AND jrnl_cd = 'A'
AND co_cd = 'RAY'
AND NOT EXISTS (SELECT 'X'
FROM gl_reversed g
WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num);

insert into MYDOCTAB(VAR1, VAR2, VAR3)
   values ('foo','bar','baz')
;
insert into MYTABLE
  select * from DOCTAB t
where t.name = 'MYDOC'
;
INSERT INTO gl_trn_DOC
( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES,
REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT,
CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM,
POST_ID_NUM, BNK_CO_CD)
SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'),
'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18),
decode(dc_cd, 'C', 'D',
'D', 'C'),
amt, TRUNC(SYSDATE), gl_trn_origin_cd,
NULL, co_unit, floor((sysdate-trunc(sysdate))*86400),
'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD,
DOC_NUM, POST_ID_NUM , BNK_CO_CD
FROM gl_trn
WHERE post_dt = to_date('&from_post_dt', 'MMDDRR')
AND jrnl_cd = 'A'
AND co_cd = 'RAY'
AND NOT EXISTS (SELECT 'X'
FROM gl_reversed g
WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num);
select * from doc; 

Output:

CODE

mikrom@mikrom-Lenovo-S500 ~/Work $ perl beilstwh_02.pl
=== SQL Stalement #1:
INSERT INTO gl_trn ( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES, REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT, CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM, BNK_CO_CD) SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'), 'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18), decode(dc_cd, 'C', 'D', 'D', 'C'), amt, TRUNC(SYSDATE), gl_trn_origin_cd, NULL, co_unit, floor((sysdate-trunc(sysdate))*86400), 'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM , BNK_CO_CD FROM gl_trn WHERE post_dt = to_date('&from_post_dt', 'MMDDRR') AND jrnl_cd = 'A' AND co_cd = 'RAY' AND NOT EXISTS (SELECT 'X' FROM gl_reversed g WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num)

		*** INSERT into table: 'GL_TRN'
=== SQL Stalement #2:
  insert into MYDOCTAB(VAR1, VAR2, VAR3)    values ('foo','bar','baz') 

		*** INSERT into table: 'MYDOCTAB'
		*** MATCHED !!! ***
=== SQL Stalement #3:
 insert into MYTABLE   select * from DOCTAB t where t.name = 'MYDOC' 

		*** INSERT into table: 'MYTABLE'
=== SQL Stalement #4:
 INSERT INTO gl_trn_DOC ( CO_CD, GL_ACCT_CD, JRNL_CD, EMP_CD_OP, POST_DT, DES, REF, DC_CD, AMT, TRN_DT, GL_TRN_ORIGIN_CD, FINAL_DT, CO_UNIT, TRN_TIME, CMNT, BNK_ACCT_CD, BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM, BNK_CO_CD) SELECT 'RAY', GL_ACCT_CD, NULL, EMP_CD_OP, to_date('&to_post_dt', 'MMDDRR'), 'REVERSAL'||' '||substr(des, 1, 30), 'R'||substr(REF, 1, 18), decode(dc_cd, 'C', 'D', 'D', 'C'), amt, TRUNC(SYSDATE), gl_trn_origin_cd, NULL, co_unit, floor((sysdate-trunc(sysdate))*86400), 'Auto-generated by DEV using ACREV', BNK_ACCT_CD , BNK_TRN_TP_CD, DOC_NUM, POST_ID_NUM , BNK_CO_CD FROM gl_trn WHERE post_dt = to_date('&from_post_dt', 'MMDDRR') AND jrnl_cd = 'A' AND co_cd = 'RAY' AND NOT EXISTS (SELECT 'X' FROM gl_reversed g WHERE g.GL_TRN_SEQ_NUM = gl_trn.gl_trn_seq_num)

		*** INSERT into table: 'GL_TRN_DOC'
		*** MATCHED !!! ***
=== SQL Stalement #5:
 select * from doc

		*** not an INSERT statement ! 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close