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.


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

Pages: [2] 1 » Show All

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

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

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

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

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

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.
Custom Search

New Articles

Incoming Search Terms for the Article

mysql replace - MySQL replace text - sql find and replace - mysql replace string - replace mysql - mysql find and replace - sql find replace - mysql replace example - sql search and replace - sql replace text - mysql update replace - mysql string REPLACE - mysql find replace - sql replace command - find and replace sql - replace in mysql - find and replace in sql - find and replace mysql - MySQL search and replace - MYSQL REPLACE SYNTAX - sql replace - find replace sql - mysql text replace - mysql replace function - sql search replace - mysql replace character - mysql find - replace mysql example - mysql find and replace text - find replace mysql - mysql replace command - sql replace string - mysql REPLACE - mysql search and replace string - replace mysql syntax - replace sql - search and replace sql - replace command in sql - mysql sql replace - update replace mysql - replace text mysql - sql find and replace query - search and replace mysql - FIND AND REPLACE IN MYSQL - mysql query replace text - mysql update replace string - mysql find replace text - replace sql mysql - search replace sql - mysql search replace -