0

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?

2
  • 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. Commented Apr 21, 2019 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
    Commented Apr 21, 2019 at 16:53

1 Answer 1

0

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.

2
  • 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
    Commented Apr 21, 2019 at 16:52
  • the you should add second sed expression to eliminate that. I updated the answer to demonstrate that as well.
    – rush
    Commented Apr 21, 2019 at 17:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .