How to Find and Replace Text in MySQL Database using SQL

MySQL database has a handy and simple string function REPLACE() that allows table data with the matching string (from_string) to be replaced by new string (to_string). This is useful if there is need to search and replace a text string which affects many records or rows, such as change of company name, postcode, URL or spelling mistake.

The syntax of REPLACE is REPLACE(text_string, from_string, to_string)

MySQL reference describes REPLACE as function that returns the string text_string with all occurrences of the string from_string replaced by the string to_string, where matching is case-sensitive when searching for from_string. text_string can be retrieved from the a field in the database table too. Most SQL command can be REPLACE() function, especially SELECT and UPDATE manipulation statement.

For example:

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);

update client_table set company_name = replace(company_name, ‘Old Company’, ‘New Company’)

The above statement will replace all instances of ‘Old Company’ to ‘New Company’ in the field of company_name of client_table table.

Another example:

SELECT REPLACE(’www.mysql.com’, ‘w’, ‘Ww’);

Above statement will return ‘WwWwWw.mysql.com’ as result.


This entry was posted on Monday, April 23rd, 2007 at 4:02 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

20 Responses to “How to Find and Replace Text in MySQL Database using SQL”

  1. Unibet
    June 30th, 2009 16:19
    20

    Thanks for sharing
    unibet

  2. wordpress修改域名后图片无法显示的解决方法 | WebBeta.org 独立博客主机
    June 27th, 2009 23:35
    19

    [...] The guide uses SQL statements based on MySQL replace() functionto modify the database. To run SQL queries, login to MySQL database that houses WordPress tables via [...]

  3. Arpit Deomurari
    May 13th, 2009 14:40
    18

    hey guys…

    I want to update my joomla cotnent with another column data

    eg.

    mysql_query(”update jos_content set introtext = replace(introtext,’XXXXXXXX’,'DATA FROM ANOTHER COLUMN’) where SECID = ‘1′ or die(mysql_error())//;

    DATA FROM ANOTHER COLUMN == SN column from same table…..

    cAN YOU PLEASE HELP ME IN CORRECT SQL SYNTAX

  4. Find and Replace in MySQL | Ruby on Rails Notes
    April 22nd, 2009 04:59
    17

    [...] I got the info from http://www.mydigitallife.info/2007/04/23/how-to-find-and-replace-text-in-mysql-database-using-sql/ [...]

  5. Filas
    March 18th, 2009 17:47
    16

    Thanks 4 the help!

  6. Leo
    March 7th, 2009 04:22
    15

    Thank you very much. Exactly what I needed!

  7. How to Move WordPress Blog to New Domain or Location [move wordpress blog] | BlogBov
    February 26th, 2009 17:13
    14

    [...] The guide uses SQL statements based on MySQL replace() function to modify the database. To run SQL queries, login to MySQL database that houses WordPress tables [...]

  8. Tim Whelan
    February 20th, 2009 17:18
    13

    When I try this I get this message…

    “Argument data type text is invalid for argument 1 of replace function”.

  9. Blogroll « Cllr. Gavin Ayling
    February 7th, 2009 06:52
    12

    [...] referred to that old domain. So I was delighted when I found a website that included the necessary SQL to find and replace. This has saved me a lot of [...]

  10. Jack
    November 29th, 2008 00:46
    11

    didn’t work for me…

  11. Clifton Griffin | Replacing Text in a MySQL Table |
    October 21st, 2008 09:42
    10

    [...] quick Google search led me to this helpful page.  In short, I had to run the following: UPDATE wp_posts SET post_content = [...]

  12. Tim
    October 16th, 2008 01:00
    9

    For me this is only working on exact replaces. I could replace all entries that are exact the searchterm but i could replace parts of an entry.

  13. martin
    October 9th, 2008 21:16
    8

    nececito reemplazar un tag con todo su relleno, por ejemplo
    update pagina_web.noticias set texto = replace(texto, “”>”,” ” )

    por vacio!!! como hago para estandalizarla y que encuetre todo lo que esta dentro de

  14. sbsxrx
    September 10th, 2008 21:52
    7

    Algo k es puede resultar practico es cuando se necesita eliminar un caracter de una sere de registros es:

    update tabla1 set campo1=replace(campo1,’8′,”);

    esta sentencia eliminaria todos los 8 de la tabla: tabla1 del registro: campo1

    (NOTA: las comillas son todas simples).

  15. Tux Training » Blog Archive » How to Move WordPress Blog to New Domain or Location
    September 1st, 2008 01:48
    6

    [...] The guide uses SQL statements based on MySQL replace() function to modify the database. To run SQL queries, login to MySQL database that houses WordPress tables [...]

  16. Monica
    June 4th, 2008 15:43
    5

    thanks, this is very helpful!!

  17. How to Move WordPress Blog to New Domain or Location » My Digital Life
    October 1st, 2007 18:23
    4

    [...] The guide uses SQL statements based on MySQL replace() function to modify the database. To run SQL queries, login to MySQL database that houses WordPress tables [...]

  18. Joost
    May 16th, 2007 07:24
    3

    Excellent, just what I needed. Thanks!

  19. yourtronica
    May 11th, 2007 10:38
    2

    Thanks for the help!

  20. masterik
    May 4th, 2007 05:48
    1

    Thanks a lot! You’ve saved my life!

Leave a Reply

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>

Subscribe to comments feature has been disabled. To receive notification of latest comments posted, subscribe to My Digital Life Comments RSS feed or register to receive new comments in daily email digest.
« Disable and Turn Off ClearType Fonts in Windows Vista
Remove or Trim First or Last Few Characters in MySQL Database with SQL »

Custom Search

New Articles

Incoming Search Terms for the Article

mysql replace - sql replace - replace mysql - mysql find and replace - sql replace command - Mysql find replace - sql find replace - mysql string replace - mysql replace string - mysql replace text - SQL replace text - REPLACE SQL - MySQL replace function - sql search and replace - mysql search and replace - sql find and replace - mySQL search replace - REPLACE IN MYSQL - sql replace string - sql replace function - mysql replace syntax - mysql replace - find and replace in sql - replace in sql - find and replace sql - mysql replace example - sql string replace - find replace mysql - find replace sql - find and replace mysql - sql replace mysql - REPLACE command in sql - SQL replace - sql replace syntax - replace sql command - replace text mysql - mysql replace command - mysql text replace - find and replace SQL query - replace text sql - MySQL Replace() - search and replace mysql - search replace mysql - mysql sql REPLACE - mysql search and replace text - mysql find - string replace mysql - mysql replace sql - MySQL REPLACE statement - sql replace all - all - mysql update replace text - REPLACE function sql - sql + replace - replace text in mysql - find replace in sql - mysql replace string function - string replace SQL - sql replace examples - replace function in sql - sql search replace - sql replace example - find and replace in mysql - sql command replace - search and replace in mysql - mysql replace text - mysql replace table - sql text replace - replace text in SQL - replace command sql - sql replace statement - sql find text - replace string sql - mysql + replace - mysql text search and replace -