in reply to Regexp in mysqldump

If you have no other backticks in the dump, you can try something like 
sub space_subst {
   my $s = shift;
   $s =~ s/ /_/g;
   return $s;
}

s/`(\w+\s+[\w\s]+)`/space_subst($1)/eg;

[download]
Perl 6 - links to (nearly) everything that is Perl 6.

Replies are listed 'Best First'.
Re^2: Regexp in mysqldump
by AnomalousMonk (Archbishop) on Apr 15, 2010 at 22:39 UTC 
    s/`(\w+\s+[\w\s]+)`/space_subst($1)/eg;

    [download]

    That gets rid of the backticks, which I think the OPer wants to preserve. 

    >perl -wMstrict -le
"my $s = q{foo `bar baz` quux};
 my $t = $s;
 $t =~ s{ ` (\w+ \s+ [\w\s]+) ` }
        { (my $r = $1) =~ s{\s}{_}xmsg;  $r }xmsge;
 print $t;
 $t = $s;
 $t =~ s{ (` \w+ \s+ [\w\s]+ `) }
        { (my $r = $1) =~ s{\s}{_}xmsg;  $r }xmsge;
 print $t;
"
foo bar_baz quux
foo `bar_baz` quux

    [download]
[reply]
[d/l]
[select]
Re^2: Regexp in mysqldump
by NetWallah (Canon) on Apr 15, 2010 at 22:44 UTC
    You may get into trouble if the default values for fields contain spaces, or if the dump includes database population (insert) statements with values containing spaces.

    Bottom line is that the substitution needs to be SQL syntax aware. I feel too lazy to write a SQL parser - there is probably one already on CPAN.

         Syntactic sugar causes cancer of the semicolon.        --Alan Perlis

[reply]

      I was creating a parser, and I already created one for another project, but then I discovered that mysqldump uses backticks (`) for table name and field names, and standard ticks (') for values in insert and update statements.

      For this reason a standard regexp does the job.

      I've tried the first solution, and works perfectly at a first glance... I'll try hopefully tonight the second one...
[reply]
[d/l]
[select]
        For this reason a standard regexp does the job.
        Nope. That reason is not enough on its own. It breaks on things like: 
        `fieldname` varchar(255) default 'Got you `haha haha`!',

        [download]
        Just because MySQL uses different quotes doesn't mean quotes cannot be contained inside other quotes.
[reply]
[d/l]

In Section Seekers of Perl Wisdom