Howdy, How about some regex help.
I'm trying to parse some SQL ddl, below is a sample of the ddl
I have a flag in my pgm that indicates to look for "create table" statements
OR anything else. The end result I'd like is that my scalar ($rebuilt) will
equal all the create table statements, or everything else.
Currently I'm splitting on ";" and looping thru the list to see if its a
create table or not. Here is my code (it works and produces the correct
output but it's ugly), furthermore I have to make another pass thru this
data (to do some other stuff). It seems like I should be able to do it with
a couple of regexs, one to null out anything not a "create table" statement,
one to null out all the "create table" statements.
But alas, I don't know how.
I run my pgm by:
# cat sample.data | tmp
Jay
#!/usr/bin/perl
undef $/;
my $file = <>;
$file =~ s/\bREM\s+//g;
$file =~ s/\n//g;
my $flag = "t";
my $rebuilt = '';
foreach my $line (split(/;/,$file)) {
$create = ($line =~ /^CREATE\s+TABLE/) ? 1 : 0;
if ($flag eq "t") {
$line = '' if ! $create;
}
else {
$line = '' if $create;
}
$rebuilt .= $line.";" if ($line);
}
foreach my $line (split(/;/,$rebuilt)) {
print substr($line,0,80)."\n";
}
Sample data:
REM CREATE TABLE "DT7333"."F00022" ("UKOBNM" CHAR(10), "UKUKID" NUMBER)
REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL
REM 81920 NEXT 57344 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
REM FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
REM "DT7333_T" ;
CREATE UNIQUE INDEX "DT7333"."F00022_PK" ON "F00022" ("UKOBNM" ) PCTFREE
10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 81920 NEXT 57344 MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "DT7333_I" NOLOGGING PARALLEL ( DEGREE 4
INSTANCES 1) ;
REM ALTER TABLE "DT7333"."F00022" ADD CONSTRAINT "F00022_PK" PRIMARY KEY
REM ("UKOBNM") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
REM STORAGE(INITIAL 81920 NEXT 57344 MINEXTENTS 1 MAXEXTENTS 2147483645
REM PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
REM TABLESPACE "DT7333_I" ENABLE ;
REM CREATE TABLE "DT7333"."F00023" ("DNCTID" CHAR(15), "DNSY" CHAR(4),
REM "DNORNN" NUMBER, "DNKCO" CHAR(5), "DNDCT" CHAR(2), "DNCTRY" NUMBER,
REM "DNFY" NUMBER, "DNOBNM" CHAR(10), "DNSMAS" CHAR(2), "DNUD01"
REM CHAR(15), "DNIDEX" NUMBER, "DNN001" NUMBER, "DNUKID" NUMBER, "DNCNR1"
REM NUMBER, "DNN002" NUMBER, "DNUKD2" NUMBER, "DNCNR2" NUMBER, "DNRANG"
REM NUMBER, "DNAUR" NUMBER, "DNIMB" CHAR(1), "DNCK01" CHAR(1), "DNSEQ"
REM NUMBER, "DNENAB" CHAR(1), "DNWLVL" NUMBER) PCTFREE 10 PCTUSED 40
REM INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 81920 NEXT 57344
REM MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST
REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DT7333_T" ;
CREATE UNIQUE INDEX "DT7333"."F00023_PK" ON "F00023" ("DNCTID" , "DNSY" ,
"DNORNN" , "DNKCO" , "DNDCT" , "DNCTRY" , "DNFY" , "DNOBNM" ) PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 81920 NEXT 57344 MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "DT7333_I" NOLOGGING PARALLEL ( DEGREE 4
INSTANCES 1) ;
CREATE INDEX "DT7333"."F00023_2" ON "F00023" ("DNKCO" , "DNDCT" ) PCTFREE
10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 81920 NEXT 57344 MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "DT7333_I" NOLOGGING PARALLEL ( DEGREE 4
INSTANCES 1) ;
CREATE INDEX "DT7333"."F00023_3" ON "F00023" ("DNSY" , "DNORNN" ) PCTFREE
10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 81920 NEXT 57344 MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "DT7333_I" NOLOGGING PARALLEL ( DEGREE 4
INSTANCES 1) ;
Jay Strauss
[EMAIL PROTECTED]
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
