Autarchy of the Private Cave

• Categories

  Select Category*nixAffiliate programsArtificial Intelligence   Agents   Machine learning   Robotics   TechnologiesBooksComparisonHardwarehow-toHumourKyivLifeLinksMiscMoviesNotepadPersonalProgramming   Ada   OS   PHP   Python   XHTML/CSSqotdRantScience   Bioinformatics   Systems BiologySocietySoftware   SecurityStartupsUkraineWeb   CMS   Drupal   WP PlugInsWelfare

• Tags list

  blog btrfs bug CMS convert data database Debian development drupal example ffmpeg FireFox fix free gallery2 git google HDD hosting how-to http linux microarray module mysql package performance PHP plugin problem project management Python R repository server shared hosting spam statistics tool Ukraine update upgrade windows wordpress

• Related entries

  1. How to replace newlines with commas, tabs etc (merge lines)
  2. Convert MySQL database from one encoding/collation into another
  3. search-hilite.php file
  4. How to fix “MySQL server has gone away” (error 2006)
  5. MySQL – Python: good MySQLdb tutorial (examples)

• Subscribe

  

  Subscribe to RSS feed.

  Desktop Reader Bloglines Feedly Live Netvibes Yahoo!

• Archives

  Select Month December 2022 October 2022 October 2018 August 2018 July 2018 September 2017 August 2017 February 2017 December 2016 June 2016 May 2016 April 2016 March 2016 February 2016 January 2016 December 2015 July 2015 May 2015 April 2015 March 2015 February 2015 October 2014 September 2014 August 2014 April 2014 March 2014 January 2014 December 2013 November 2013 October 2013 August 2013 July 2013 June 2013 August 2012 June 2012 May 2012 February 2012 January 2012 December 2011 October 2011 August 2011 May 2011 April 2011 March 2011 February 2011 January 2011 December 2010 November 2010 October 2010 September 2010 August 2010 July 2010 June 2010 May 2010 April 2010 March 2010 February 2010 January 2010 December 2009 November 2009 October 2009 September 2009 August 2009 July 2009 June 2009 May 2009 April 2009 March 2009 February 2009 January 2009 December 2008 November 2008 October 2008 September 2008 August 2008 July 2008 June 2008 May 2008 April 2008 March 2008 February 2008 December 2007 November 2007 October 2007 September 2007 August 2007 July 2007 June 2007 May 2007 April 2007 March 2007 February 2007 January 2007 December 2006 November 2006 October 2006 September 2006 August 2006 July 2006 June 2006

• Recent comments

  • JT on How to truncate git history (sample script included)
  • Charlotte Semaan on How to fix: Xiaomi Mi Band stopped tracking steps and sleep
  • SAM on How to fix: Xiaomi Mi Band stopped tracking steps and sleep
  • SY on Convert MySQL database from one encoding/collation into another
  • Bogdan on Citation and reference managers for Linux: short overview

• Meta

  • Log in

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

This entry was posted on Tuesday, October 27th, 2009 at 9:37 and is filed under how-to, Notepad. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply