Regex substitution in perl

Question

I have a string that needs to be inserted before semi-colons(;) in all create table ddls found in a file.

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

The output would be:

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
BLKSIZE 2048;

Now there are multiple create table ddls in the file and there are other ddls as creating catalog, schemas, views etc. I only want that the string to be inserted is only for the create table stmt.

I tried the following:

sub read_file {
        my ($filename) = @_;
        print "$filename \n";
        open my $in, '<', $filename or carp "Could not open '$filename' for reading $!";
        local $/ = undef;
        my $all = <$in>;
        close $in;
        return $all;
}   

sub write_file {
        my ($filename, $content) = @_;
        open my $out,'>', $filename or carp "Could not open '$filename' for writing $!";
        print $out $content;
        close $out;
        return;
}   

sub changeddl{
        my $data = read_file($_);
        print "$data \n";
        if($data=~m/\s*Create\s*Table.*/im){
                $data=~s/(\s*Create\s*Table).*(;$)/$1 blsize 2048 $2/gmi;
                write_file(($_, $data))
        }
}       

Then i call call changeddl with a file(which contains the ddl, but it wont work.) pls help.

Answer 1

I've changed approach a bit, there is no need to match and substitute, as you can do test/substitution in one step,

Matching Create\s*Table and everything after that is not ; char, using positive look behind \K

if ($data =~ s/Create\s*Table[^;]+\K/blsize 2048/gi) {
   write_file(($_, $data))
}

Answer 2

You should use the s (single line) flag instead of gm.
You should also put the dotall token inside your first capture group.

Like so:

s/(\s*Create\s*Table.*?);/$1BLKSIZE 2048;/si