I have a huge mysql dump that I'm using sed to delete unneeded rows (very much faster than deleting from table) and each line of the dump is in the format

 INSERT INTO table_name VALUES ("somevalue", 0), ("anothervalue", 90), ("nospaces", 67), ("another", 0) ... ;

I only need rows with the 0 integer value, so I use this expression:

 sed -i 's/, ("[^ ]*", [1-9][0-9]*)//g' dump.sql

However, this doesn't match the very first row (see the comma in the expression) .. Is there anyway I can match either the comma in the beginning or end, but not both?

  • This should work fine, the only wrinkle can come from the spaces might be TABs. Use, [[:blank:]] in place of a space in your regex. – Rakesh Sharma Apr 21 at 16:51
  • @RakeshSharma it's the commas that are causing the problem. If I have a leading or trailing comma, it is no longer valid MySQL syntax. – Jacob John Apr 21 at 16:53

I suggest that you don't rely on , in front of (), but instead rely on it after with \?. It doesn't really matter whether you have one or multiple spaces anyway. Something like that should do it for you:

sed -i 's/("[^ ]*", [1-9][0-9]*),\?//g; s/,\s*;/;/' dump.sql

Note that \? means that , is not a mandatory character. If it's not there, there still would be a match.

  • Thank you for your answer. If the expression matches the very last row then I'll be left with an extra comma ... ("val", 0), ("anotherval",0),; which is incorrect MySQL syntax :( – Jacob John Apr 21 at 16:52
  • the you should add second sed expression to eliminate that. I updated the answer to demonstrate that as well. – rush Apr 21 at 17:08

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.