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.

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

  1. DjZoNe
    November 27th, 2009 21:49
    31

    This was pretty useful when I migrated standalone blogs to WPMU, and I needed to replace bunch of media file paths.

    Thanks.

  2. Vil Gays
    November 24th, 2009 00:00
    30

    @Richard:
    Just a guess: are you using regular single quotes (i.e. apostrophe) as you should, or maybe typographic ones, which you shouldn’t?

  3. Richard
    November 20th, 2009 14:16
    29

    I’m moving my blog to a new hosting company. Since I’m moving the blog to the root from a subfolder /blog/, I need to change the wp_posts guid field on all the posts.
    Example: http://mymarketingvp.net/blog/18-revision-5/ [This is from the current database.]

    Following the steps above, I entered this SQL statement:
    update wp_posts set guid = replace(guid, ‘net/blog/’, ‘net/’)
    ["net" is the end of the domain name *.net]

    I got the following error message:
    Error
    SQL query:
    UPDATE wp_posts SET guid = replace( guid, ‘net / blog / ’, ‘net / ’ )
    MySQL said:
    #1054 – Unknown column ‘‘net’ in ‘field list’

    Notice how it added some spaces in the command. Same error when I remove the spaces.

    Any suggestions??? Thanks!

  4. timtak
    October 28th, 2009 09:00
    28

    I am looking for a way to do this globally accross a whole database.

  5. kevin
    October 20th, 2009 20:28
    27

    Doesn’t work at all for replacing web addresses within a database.

  6. vishwas
    September 30th, 2009 15:26
    26

    Wanted to use REPLACE command to use for a column of a table…and this helped me…thanks

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

    Thanks. :)

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

    thanks men for the help

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

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

    Thanks!

    I used this

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

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

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

    Thanks for sharing
    unibet

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

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

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

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

    Thanks 4 the help!

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

    Thank you very much. Exactly what I needed!

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

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

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

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

    didn’t work for me…

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

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

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

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

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

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

    thanks, this is very helpful!!

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

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

    Excellent, just what I needed. Thanks!

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

    Thanks for the help!

  31. 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 - mysql string replace - mysql find and replace - sql replace text - mysql replace string - mysql update replace - find string sql - how to update mysql text column - mysql find and replace select query - mysql replace example - mysql replace text in field - replace character in mysql - replace command in sql - search and replace mysql - sql replace example - sql statement search and replace - sql search and replace - mysql replace alpha characters - replace string mysql - sql query to remove text from database - changes mysql database string - find and replace sql - find and replace mysql database string - find and replace using MyQL - find and replace in mysql query - find and replace mysql - how to do a search in SQL - how to replace the string using update query in mysql - mysql remove character from string - mysql search and replace - mysql replace value field - mysql replace tutorial - mysql where replace command - mysql query find and replace - mysql text _replace - mysql search and replace character - mysql select fields change - mysql search and replace text - mysql replace values - mysql REPLACE syntax - mysql Replace command syntax - mysql +replace - mysql query remove character from field - mysql string functions replace - mysql update replace string - remove characters from field mysql - remove white space in column using mysql - replace text in database - replace text in mysql table - replace in database mysql -