Performing Complex Edits of Data (Cleaning up Data)

Hi
Database designers often find themselves lumped with the job of cleaning up data may be imported from some where else. Well you can use mysql replace command to do some of the work. However for really complex stuff, I dump the database to a sql file and then modify that with vim or perl as both of them allow regular expressions.

I use my favorite MySQL GUI is www.sqlyog.com to do the saving and reloading of the data.

Maintaining data with replace

Hi

The following can be used to do a substitute of text.

In the following example full website URL's are being substituted by relative

update tbl_websitepages set page_content=replace (page_content,'http://www.website.co.uk/website/cms/','/website/images/cms/');

Unfortunately no regular expressions are possible

Resetting an auto increment field

If ever you wanted to reset an autoincrement field

ALTER TABLE tbl_address AUTO_INCREMENT = 100;

There are restrictions however

Learn more here