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 4th 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 nth 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
  • 2
    Why not SELECT <fields you want> INTO <new table>? – DopeGhoti Dec 12 '16 at 17:59
  • Essentially you need a SQL parser... I mean, you could have: … VALUES ('0', '1'',''abc', '2') and that's going to trip up your regexp, most likely. – derobert Dec 12 '16 at 18:48

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]

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;
            ( $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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.