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.

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

Pages: 1 2

  1. Maksin
    September 15th, 2009 19:14
    25

    Thanks. :)

  2. Peter
    September 7th, 2009 20:44
    24

    thanks men for the help

  3. Simon
    September 1st, 2009 14:08
    23

    This is pretty good. I use replace function very often. somehow I wish it had the capability to replace multiple values (different values) at once without having to nest them.

  4. Joe
    July 14th, 2009 08:08
    22

    Thanks!

    I used this

  5. morse
    July 8th, 2009 22:11
    21

    Am very thankful, The code helped me. Google diverted me to this post. Thank you again.

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

    Thanks for sharing
    unibet

  7. 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 [...]

  8. 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

  9. 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/ [...]

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

    Thanks 4 the help!

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

    Thank you very much. Exactly what I needed!

  12. 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 [...]

  13. 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”.

  14. 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 [...]

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

    didn’t work for me…

  16. 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 = [...]

  17. 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.

  18. 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

  19. 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).

  20. 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 [...]

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

    thanks, this is very helpful!!

  22. 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 [...]

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

    Excellent, just what I needed. Thanks!

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

    Thanks for the help!

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

    Thanks a lot! You’ve saved my life!

Pages: 1 2

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 My Digital Life Comments RSS feed to receive notification of latest comments posted.
« Disable and Turn Off ClearType Fonts in Windows Vista
Remove or Trim First or Last Few Characters in MySQL Database with SQL »

New Articles

Incoming Search Terms for the Article

mysql replace - mysql replace string - mysql replace text - sql find and replace - replace mysql - mysql find and replace - mysql string replace - mysql replace example - sql replace text - sql find replace - replace in mysql - mysql search and replace - sql search and replace - mysql update replace - mysql REPLACE syntax - find and replace sql - mysql search and replace text - replace mysql example - mysql find replace - mysql replace function - find and replace mysql - mysql find and replace text - mysql text replace - mysql replace character - find and replace sql query - Find and replace in SQL - find replace mysql - sql replace string - sql replace mysql - replace text mysql - sql replace command - sql search replace - search and replace mysql - find replace sql - mysql replace text in field - replace command in sql - replace string mysql - mysql find - sql replace - mysql sql replace - mysql find replace text - mysql search replace - mysql replace - update replace mysql - find and replace in mysql - mysql replace command - mysql replace string in field - REPLACE character mysql - find and replace in sql query - search and replace sql - sql query replace string - replace sql - sql query replace - mysql replace sql - mysql find and replace string - sql search and replace query - sql text replace - string replace mysql - sql replace syntax - mysql update string replace - mysql character replace - replace string in mysql - mysql remove character from string - replace text in mysql table - sql replace example - replace sql syntax - sql search replace string - find and replace mysql query - mysql sql replace function - mysql replace char - mysql search replace text - replace mysql syntax - FIND REPLACE IN mysql - mysql update replace character - replace text in mysql -