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.

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

Pages: « 2 [1] Show All

  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: « 2 [1] Show All

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 find and replace - MySQL replace text - replace mysql - mysql replace string - sql find replace - mysql replace example - mysql find and replace - sql search and replace - sql replace text - mysql string REPLACE - sql replace command - mysql update replace - mysql find replace - MySQL search and replace - replace in mysql - find and replace sql - find replace mysql - find replace sql - MYSQL REPLACE SYNTAX - find and replace mysql - mysql replace character - mysql find - replace mysql example - find and replace in sql - mysql search and replace string - mysql replace function - sql search replace - mysql replace command - sql replace string - sql replace - update replace mysql - mysql find and replace text - mysql text replace - mysql find replace text - replace mysql syntax - mysql replace text in field - sql command replace - mysql replace string function - sql text replace - mysql sql replace - mysql select replace - mysql REPLACE - replace text sql - replace sql - search and replace mysql - sql replace example - sql search and replace query - replace command in sql - replace command sql -