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.

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

Pages: 1 2

  1. Stuart
    December 18th, 2009 02:52
    32

    Hi Thanks, just the ticket :)

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

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

  4. 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!

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

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

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

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

  7. 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: 1 2

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 »

New Articles

Incoming Search Terms for the Article

mysql replace - mysql replace string - mysql replace text - sql find and replace - replace mysql - mysql find and replace - mysql string replace - mysql replace example - sql replace text - sql find replace - replace in mysql - mysql search and replace - sql search and replace - mysql update replace - mysql REPLACE syntax - find and replace sql - mysql search and replace text - replace mysql example - mysql find replace - mysql replace function - find and replace mysql - mysql find and replace text - mysql text replace - mysql replace character - find and replace sql query - Find and replace in SQL - find replace mysql - sql replace string - sql replace mysql - replace text mysql - sql replace command - sql search replace - search and replace mysql - find replace sql - mysql replace text in field - replace command in sql - replace string mysql - mysql find - sql replace - mysql sql replace - mysql find replace text - mysql search replace - mysql replace - update replace mysql - find and replace in mysql - mysql replace command - mysql replace string in field - REPLACE character mysql - find and replace in sql query - search and replace sql - sql query replace string - replace sql - sql query replace - mysql replace sql - mysql find and replace string - sql search and replace query - sql text replace - string replace mysql - sql replace syntax - mysql update string replace - mysql character replace - replace string in mysql - mysql remove character from string - replace text in mysql table - sql replace example - replace sql syntax - sql search replace string - find and replace mysql query - mysql sql replace function - mysql replace char - mysql search replace text - replace mysql syntax - FIND REPLACE IN mysql - mysql update replace character - replace text in mysql -