Remove two columns of a SQL insert command in a dump file

Question

I have a SQL dump file having several insert commands. I would like to remove, through sed or grep (or even another simple bash trick), a specific column and its equivalent data at the VALUES clause. Thus:

INSERT INTO "capt" ("fid_c1","id","fid_capt","mslink","capt", ...) VALUES ('0','0','24','189','CAP.FU1', ...);

Sed or grep shall remove the "mslink" column and its correspondent value "189" — both are at the 4^th position. Worth noting I don't know the original position of the undesired column and, so, the regular expression must use some kind of memory to drop the n^th value of the query, being n the position of the "mslink".

An incomplete sed command would be like this:

sed -re 's/INSERT INTO "capt" \((.*,?)*"mslink",?(.*,?)*\) VALUES \((.*,?)+\);/INSERT INTO "capta" (\1\2) VALUES (\3)/' file.sql

Answer 1

I could not think of perl one-liner for the task but here is a perl script that will do what you want.

Change $match to what you want to search. The script loops through the file and prints the resulting file to standard output. Pipe that result to a file and you'll have your change. I meant to comment the code more. Sorry about that.

The script relies on the "VALUES" keyword, the data being in the parenthesis and being comma delimited. It will probably fail otherwise.

Command to run if the code is saved as 'sql_parser.pl' %> perl sql_parser.pl [file]

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

sub usage
{
    print "usage: sql_parser.pl [file]\n";
}

# load the input file
sub load
{
    my $file = shift;
    open my $in, "<", $file or die "unable to open $file as input $!";
    my @data = <$in>;
    close $in;
    foreach (@data) { chomp; s/\cM//g; }
    return @data;
}

# what to search for
# could supply this parameter on the command line too
# my $match = shift; 
my $match = 'mslink';

# get the file to process on the command line
my $file = shift;
{
    # load the data
    my @lines = &load($file);

    #print "$_\n" foreach (@lines);

    # loop through the data in the file
    foreach (@lines)
    {
        # match the line of text
        my @f = m/(.*?\() (.*?) (\)\s+?VALUES\s+?\() (.*?) (\).*?$)/x;
        if (@f)
        {
            my @cmds = split /,/, $f[1];
            my @nums = split /,/, $f[3];
            my $matched = 0;
            for ( my $i = 0; $i < @cmds; ++$i )
            {
                if ( $cmds[$i] =~ /$match/ )
                {
                    #print "$cmds[$i]\n";
                    undef $cmds[$i];
                    undef $nums[$i];
                    $matched = 1;
                    last;
                }
            }
            ( $f[1] = join ',', @cmds ) =~ s/\,\,/,/;
            ( $f[3] = join ',', @nums ) =~ s/\,\,/,/;
            if ($matched)
            {
                $_ = join ' ', @f;
            }
        }
    }
    print "$_\n" foreach (@lines);
}

I ran this script on this data:

INSERT INTO "capt" ("fid_c1","id","fid_capt","mslink","capt", ...) VALUES ('0','0','24','189','CAP.FU1', ...);

$ perl sql_parser.pl test.dat The output is below:

INSERT INTO "capt" ( "fid_c1","id","fid_capt","capt", ... ) VALUES ('0','0','24','CAP.FU1', ... );

To capture the output: $ perl sql_parser.pl test.dat > capture.txt