Thursday, October 23, 2008
Apply a replacement regexp on an entire mySQL table
You may know the way to select specific rows matching a pattern in mySQL.
Indeed we can use this syntax :
SELECT * FROM `table` WHERE `field` REGEXP ’^my regexp’;
But I looked a long time for a way to apply a replacement regexp on a table field.
Finally I had to make a shell script do to that because it’s not implemented in mysql ...
That is the script I wrote :
IFS=’
’for i in $(echo ‘SELECT id_field, field FROM `my_table` ;’ | mysql mydatabase);do
echo $i | sed -n ‘s/\([0-9]*\).*some_thing_to_remove(.*\)/ UPDATE `my_table` SET field = ”\2” WHERE id_field = \1/p’ | mysql mydatabase
done
That is absolutely not an optimized script, but it works and it is very useful to manage a table when the number of rows is not too big.
If you have a better example to do that don’t hesitate to share it with us !