I am having some trouble with running with some MySQL INSERT
statements in a test database, so I thought of stripping some problematic fields which seem to be causing the issue.
INSERT INTO `participants` VALUES
(981,2,_binary '9d3d6446fbde55e205288272fd2b≠Z…∆ƒÿ¥ÿ™+FºtΩ€j"A≠üˆQ% Xd¢£„˘«',_binary '401e658ecc69d4aae24dMπ1Aºr—!µÉ⁄±y§~€W√∆Ë%∫5∂as',_binary '4f05550201332<è›LsåÇ&ÂÀî!Ò„à9ÅümÉ®◊ˇˇ',_binary '02fe`6 råFÃÈWXiˆÂ¶üÏuŒøD˚˛NwœSÊÍn',_binary '9fc0eßαÓ]DÁÁ,üæù¢‰¬3âC›X€r–í§4',_binary '942ûuq]∂ÁîÍPU“ríL˚∆IÑü 0¶Ó˜FVÁ—∂_ÿ}›∂{¨$„éH˚’i',NULL,NULL,0,'2018-07-19 08:25:01',NULL,'2018-07-19 08:25:01',NULL),
(982,2,_binary '7a8e¬N≥±;~ã“+rÁ/Ö≠µµ'1˝ãs_”ıu ',_binary 'a6840˙̇Ï?¨ì“_›ü¿ØQÓ=0fib!Ì»XE^—',_binary '799f5èküÈ}π@æ†â㣯fißq$ÍÃ∏oÌP,] nv',_binary 'b65ed⻘öZ&Ræ±ôËk ÷ÌriV◊∫±ô¢NS]Ã',_binary 'f7c42c71«w±˛T€Lœ{Íx.Ù^/¥ììépQîÌ]~—xAe«',_binary '8b3621ÿÉDåäíûL≈À#_êÉ¢sÓˆÈrÕ…æë +`ölÓïY/E‡z{∂+ûÁ¬¢',NULL,NULL,0,'2018-07-19 08:32:52',NULL,'2018-07-19 08:32:52',NULL),
(984,3,_binary 'bd7ÖWì¨1~ü[¿Ú4¨¢XQ€ø@§±√ta?y±™',_binary 'ee36b49eaffa8d02260615c3ac215dcfe73a39a4fc1a295acb92a2e08e7d9621鯣.¥¬†Ê$fl}@©Áí¬≈fÏÿU¡DAHı`≈',NULL,NULL,NULL,_binary '762fyé∞¡,©\ô —›G‡#,í∑lïß埥k˛ÍflâÔ¥u,≤£M˛ø‚€!Zûs4»§oÈÑΩ`yC=0ösnv',NULL,NULL,0,'2018-07-19 08:37:33',NULL,'2018-07-19 08:37:33',NULL);
The problem are those varbinary
fields that contain all sorts of weird characters that cause the insert statement to fail.
Since I’ll only use the data for testing, I’m not concerned about losing information.
I would like to replace all those _binary 'somethingsomething'
fields with simply NULL.
So the end result should be:
INSERT INTO `participants` VALUES
(981,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,'2018-07-19 08:25:01',NULL,'2018-07-19 08:25:01',NULL),
(982,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,'2018-07-19 08:32:52',NULL,'2018-07-19 08:32:52',NULL),
(984,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,'2018-07-19 08:37:33',NULL,'2018-07-19 08:37:33',NULL);
So far I’ve come up with this Regex:
_binary '.*'
but it matches all the _binary
fields at once, until the end of the line.
I’ve also tried modifying the regex to somehow delimit the string with commas (I.E. _binary '.*',
, ,_binary '.*'
, ,_binary '.*',
) but none of these seem to help.
How can I modify the Regex to achieve my goal?