Autarchy of the Private Cave

Tiny bits of bioinformatics, [web-]programming etc

    Search and replace in a MySQL table

    27th October 2009

    This query performs a table-wide search-and-repalce:

    UPDATE `table_name` SET `table_field` = REPLACE(`table_field`,’string to search for and replace’,'replacement string’);

    If you need a database-wide search-and-replace, you could try this script (I haven’t tested/used it myself).

    Beware of the following gotchas:

    1. wrong query syntax may ruin the field you are performing replace on, so always backup first!
    2. be sure to provide “search-for” string as specific as possible, or you will get unexpected replacements (e.g. replacing mini with little will also convert all minivans into littlevans); also, do use WHERE clause when necessary to limit the number of rows modified
    3. the function in the example is case-sensitive, so replacing all minivans with vehicles won’t replace Minivans. However, I believe there exists a case-insensitive version of REPLACE function
    Share

    Leave a Reply

    XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>