Sal00m Sal00m - 7 months ago 20
Bash Question

Delete lines from file using sed containing different patterns

I have a dump from a production DB but i want to remove data of some tables like messages, messages_files, etc because are not useful to debug/programming on local.

I have been using this command to remove the lines containing this kind of data:

sed -i '/CREATE DATABASE/d' $current_main_db.sql &&
sed -i '/USE \`okn/d' $current_main_db.sql &&
sed -i '/INSERT INTO \`messages\`/ d' $current_main_db.sql &&
sed -i '/INSERT INTO \`messages_email_cron\`/ d' $current_main_db.sql &&
sed -i '/INSERT INTO \`messages_users\`/ d' $current_main_db.sql &&
sed -i '/INSERT INTO \`messages_files\`/ d' $current_main_db.sql &&
sed -i '/INSERT INTO \`messages_mail_list\`/ d' $current_main_db.sql &&
sed -i '/INSERT INTO \`messages_sms_cron\`/ d' $current_main_db.sql &&
sed -i '/INSERT INTO \`messages_tags\`/ d' $current_main_db.sql &&
sed -i '/INSERT INTO \`messages_temp_receivers\`/ d' $current_main_db.sql &&
sed -i '/INSERT INTO \`messages_threads\`/ d' $current_main_db.sql;


It works well but is very slow so i try to combine all the patterns into one
sed
command. I read the manual and find this:


regexp1\|regexp2

Matches either regexp1 or regexp2. Use parentheses to use complex alternative regular expressions. The matching process tries each alternative in turn, from left to right, and the first one that succeeds is used. It is a GNU extension.


So i tried this:

sed -i '/CREATE DATABASE\|USE \`okn\|INSERT INTO \`messages\`\|INSERT INTO \`messages_email_cron\`\|INSERT INTO \`messages_users\`\|INSERT INTO \`messages_files\`\|INSERT INTO \`messages_mail_list\`\|INSERT INTO \`messages_sms_cron\`\|INSERT INTO \`messages_tags\`\|INSERT INTO \`messages_temp_receivers\`\|INSERT INTO \`messages_threads\`/ d' $current_main_db.sql;


But do not work, i tried to use parenthesis for every pattern without any luck:

sed -i '/(CREATE DATABASE\|USE \`okn)\|(INSERT INTO \`messages\`)\|(INSERT INTO \`messages_email_cron\`)\|(INSERT INTO \`messages_users\`)\|(INSERT INTO \`messages_files\`)\|(INSERT INTO \`messages_mail_list\`)\|(INSERT INTO \`messages_sms_cron\`)\|(INSERT INTO \`messages_tags\`)\|(INSERT INTO \`messages_temp_receivers\`)\|(INSERT INTO \`messages_threads\`)/d'


Am i doing something wrong?

I search in SO and find some similar questions but do not work for me.

Answer

grep should suffice:

grep -vE '^(INSERT INTO `messages(_email_cron|_users|_files|_mail_list|_sms_cron|_tags|_temp_receivers|_threads)`|CREATE DATABASE|USE `okn)' file