how to regex remove this?

How to write a regex to remove all SQL insert statements for Drupal's cache tables?

• I want to write a regular expression that would remove all SQL insert statements for Drupal's cache tables like cache, cache_bootstrap, cache_bootstrap etc. from a database dump file. I was basically trying to write a regex that would find a text that begins with "INSERT INTO `cache" and ends with a semicolon. But, I am not able to figure out proper regex for the same. Any ideas? Following is my code: <?php $sql=file_get_contents("C:\Users\akulkarni\Desktop\drupal7.sql"); $matches = array(); preg_match_all('/INSERT INTO `cache([^.]+)`.*?/s', "$sql",$matches , PREG_PATTERN_ORDER); var_dump($matches); ?> Followins is the sample text (trimmed): 'INSERT INTO cache (cid, data, expire, created, serialized) VALUES ('ctools_plugin_files:ctools:content_types', 0x613a323a7b733a363a2263746f6f6c73223b613a35333a7b733a31363a22766f636162756c6172795f7465726d73223b4f3a383a22737464436c617373223a333a7b733a333a22757269223b733a38363a2273697465732f616c6c2f6d6f64756c65732f63746f6f6c732f706c7567696e732f636f6e74656e745f74797065732f766f636162756c6172795f636f6e746578742f766f636162756c6172795f7465726d732e696e63223b733a383a2266696c656e616d65223b733a32303a22766f6'); 'INSERT INTO cache_bootstrap (cid, data, expire, created, serialized) VALUES ('bootstrap_modules', 0x613a323a7b733a353a2264626c6f67223b4f3a383a22737464436c617373223a323a7b733a343a226e616d65223b733a353a2264626c6f67223b733a383a2266696c656e616d65223b733a32363a226d6f64756c65732f64626c6f672f64626c6f672e6d6f64756c65223b7d733a373a226f7665726c6179223b4f3a383a22737464436c617373223a323a7b733a343a226e616d65223b733a373a226f7665726c6179223b733a383a2266696c656e616d65223b733a33303a226d6f64756c65732f6f7665726c61''); 'INSERT INTO cache_field (cid, data, expire, created, serialized) VALUES ('field:node:1', 0x613a35303a7b733a32313a226669656c645f636f72655f6f726967696e61746f72223b613a313a7b733a333a22756e64223b613a313a7b693a303b613a333a7b733a353a2276616c7565223b733a35363a2243616c69666f726e6961204469766973696f6e206f66204f696c2c2047617320616e642047656f746865726d616c205265736f7572636573223b733a363a22666f726d6174223b4e3b733a31303a22736166655f76616c7565223b733a35363a2243616c69666f726e6961204469766973696f6e206f66204f696''); 'INSERT INTO cache_form (cid, data, expire, created, serialized) VALUES ('form_form-0LSlVPgTs2A_g3iXF6RIK1wsrfEqlFO_0BK971FBCEw', 0x613a32333a7b733a31313a222361747472696275746573223b613a313a7b733a353a22636c617373223b613a313a7b693a303b733a32303a2266656174757265732d6578706f72742d666f726d223b7d7d733a383a222366656174757265223b4e3b733a343a22696e666f223b613a373a7b733a353a222374797065223b733a383a226669656c64736574223b733a353a222374726565223b623a303b733a343a226e616d65223b613a363a7b733a363a22237469746c652''); 'INSERT INTO cache_bootstrap (cid, data, expire, created, serialized) VALUES ('links:features:all:0:en:0', 0x613a323a7b733a393a226d696e5f6465707468223b693a313b733a393a226d61785f6465707468223b4e3b7d, 0, 1316550799, 1), ('links:features:tree-data:en:ec99d3452fef1ede622e66c68ba908b1dad455aa71f5e68648aeec6488b89c88', 0x613a323a7b733a343a2274726565223b613a303a7b7d733a31303a226e6f64655f6c696e6b73223b613a303a7b7d7d, 0, 1316550799, 1), ('links:main-menu:all:0:en:0', 0x613a323a7b733a393a226d696e5f6465707468223'');

• Answer:

  You can exclude tables from the database dump by using the http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_ignore-table parameter. This regular expression will help you locate the INSERT statements INSERT\s+INTO\s+(cache|cache_bootstrap|cache_field|cache_form)[^\;]+