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.

Share and contribute or get technical support and help at My Digital Life Forums.



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.

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

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

    Thanks a lot! You’ve saved my life!

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

    Thanks for the help!

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

    Excellent, just what I needed. Thanks!

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

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

    thanks, this is very helpful!!

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

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

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

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 without commenting

« 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 -