Remove certain section from the line

Shell Programming and Scripting
1

A typical line looks like this...

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=129  COMMENT='Compiled E-Mails';

I want to remove DEFAULT CHARSET= and COLLATE= after resetting AUTO_INCREMENT=0
I do not want to change the engine and comment.

2

Assuming you want it to cope with the order of params changing:

sed 's/DEFAULT CHARSET=[^ ]* //;s/COLLATE=[^ ]* //;s/AUTO_INCREMENT=[^ ]* /AUTO_INCREMENT=0/'
3

Thanks. But it it does not work when the line does not has the auto_increment clause. In the example, the third line is wrong. There should have been no "Default charset".

>> cat test.txt
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=482 COLLATE=utf8 COMMENT="testing deletion using sed";
) ENGINE=InnoDB AUTO_INCREMENT=482 DEFAULT CHARSET=utf8 COLLATE=utf8 COMMENT="testing deletion using sed";
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

>> sed 's/DEFAULT CHARSET=[^ ]* //;s/COLLATE=[^ ]* //;s/AUTO_INCREMENT=[^ ]* /AUTO_INCREMENT=0 /' test.txt
) ENGINE=InnoDB AUTO_INCREMENT=0 COMMENT="testing deletion using sed";
) ENGINE=InnoDB AUTO_INCREMENT=0 COMMENT="testing deletion using sed";
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
4

try this:

$more file10
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=482 COLLATE=utf8 COMMENT="testing deletion using sed";
) ENGINE=InnoDB AUTO_INCREMENT=482 DEFAULT CHARSET=utf8 COLLATE=utf8 COMMENT="testing deletion using sed";
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


awk '
{
    for (word=1;word <NF; word++)
    {
        if  (index($word,"DEFAULT") || index($word, "CHARSET=") || index($word, "COLLATE="))
            $word=""
        else
        if  (index($word, "AUTO_INCREMENT="))
            $word="AUTO_INCREMENT=0"
    }
    print $0 

}
' file10

Result:
) ENGINE=InnoDB   AUTO_INCREMENT=0  COMMENT="testing deletion using sed";
) ENGINE=InnoDB AUTO_INCREMENT=0    COMMENT="testing deletion using sed";
) ENGINE=MyISAM  CHARSET=latin1;
5

Thanks chipcmc.
But as shown in your own example, the CHARSET=latin1; is still there on line 3. The lines 1 and 2 are OK.

6

Yes is true, the problem is the bucle for xD the condition is <NF ;)...change for <=NF and everything is OK ..

7

I see the issue, I'd not thought about the end of line character:

sed 's/DEFAULT CHARSET=[^ \;]*//;s/COLLATE=[^ \;]*//;s/AUTO_INCREMENT=[^ \;]*/AUTO_INCREMENT=0 /'
8

I tried both the commands and the only difference is the missing ';' from the last line while using awk

$ sed 's/DEFAULT CHARSET=[^ \;]*//;s/COLLATE=[^ \;]*//;s/AUTO_INCREMENT=[^ \;]*/AUTO_INCREMENT=0 /' test.txt
) ENGINE=InnoDB  AUTO_INCREMENT=0   COMMENT="testing deletion using sed";
) ENGINE=InnoDB AUTO_INCREMENT=0    COMMENT="testing deletion using sed";
) ENGINE=MyISAM ;


$ awk '
{
    for (word=1;word <=NF; word++)
    {
        if  (index($word,"DEFAULT") || index($word, "CHARSET=") || index($word, "COLLATE="))
            $word=""
        else
        if  (index($word, "AUTO_INCREMENT="))
            $word="AUTO_INCREMENT=0"
    }
    print $0 

}
' test.txt
) ENGINE=InnoDB   AUTO_INCREMENT=0  COMMENT="testing deletion using sed";
) ENGINE=InnoDB AUTO_INCREMENT=0    COMMENT="testing deletion using sed";
) ENGINE=MyISAM