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.



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


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 -