How to Find and Replace Text in MySQL Database using SQL

HomeยปSoftwareยปDatabasesยป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 use REPLACE() function, especially SELECT and UPDATE manipulation statement.

For example, the syntax for UPDATE SQL command with using REPLACE function:

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'find this string', 'replace found string with this string');

As an example:

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 for SQLECT query:

SELECT REPLACE('www.mysql.com', 'w', 'Ww');

Above statement will return ‘WwWwWw.mysql.com’ as result. The REPLACE function is multibyte safe.

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years. Connect with LK through Tech Journey on Facebook, Twitter or Google+.
  • Thanks a lot! You've saved my life!

  • Thanks for the help!

  • Joost

    Excellent, just what I needed. Thanks!

  • Monica

    thanks, this is very helpful!!

  • sbsxrx

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

  • martin

    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

  • Tim

    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.

  • didn't work for me…

  • Tim Whelan

    When I try this I get this message…

    "Argument data type text is invalid for argument 1 of replace function".

  • Leo

    Thank you very much. Exactly what I needed!

  • Thanks 4 the help!

  • 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

  • Unibet

    Thanks for sharing

    unibet

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

  • Joe

    Thanks!

    I used this

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

  • thanks men for the help

  • Thanks. ๐Ÿ™‚

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

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

  • timtak

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

  • 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!

  • Vil Gays

    @Richard:

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

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

    Thanks.

  • Stuart

    Hi Thanks, just the ticket ๐Ÿ™‚

  • This worked great for removing all the  characters that showed up on my wordpress blog after migrating my database into a mysql 5.0 db. Thank you!

  • fisherw

    Remember that when using replace() to update a field (like the old company / new company example), the length of the field does not necessarily expand to fit the new content.

  • Robert

    I need to replace some old links that are in the database records that used some javascript and contain % characters as part of the string. I can't seem to get replace to recognize these and I am assuming it treats them like a wildcard char in mysql. I've tried escaping % etc but not getting results.

    any suggestions

    example (these are not intended to be wildcard characters):

    update tablename set content = replace(content,'%200,%210,%200','');

  • Josh

    this helped me a lot, thanks! :]

  • Kim Woodbridge

    Thanks! Just what I was looking for.

  • Pingback: Solution To WordPress getimagesize() Error « kyleabaker.com()

  • ASP.NET SQL help

    Great! Problem solved.

  • Craig

    Worked fine for me for replacing web addresses in a field.

    I had a shitload of posts where I needed to replace an affiliate link from one url to another and it didn't have any issues for me.

    Didn't even know you could do such a thing until I thought perhaps google might light my way – right to this blog – Cheers ๐Ÿ™‚

  • cili

    I didn't know mySQL has this funtion.

    Worked me a lot.

    Thanks!

  • Gleedo

    Hello, following a forum conversion, I have a number paths to smilies that are totaly wrong, so I need to update these paths in a table field.

    Now I tried the following in a test database:
    update vb4_post set pagetext = replace(pagetext, &lsquo ;http://www.theoldgitsarmy.com/forum/public/style_emoticons//’, &lsquo ;http://www.theoldgitsarmy.com/forums/images/smilies/’);

    But received an error:
    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '://www.theoldgitsarmy.com/forum/public/style_emoticons//’, ‘http:' at line 1

    I am a total noob at this so am really stuck. Any help really appreciated

  • Pete – Shore

    Just had to do a mass replace on a table of incorrect data, cheers for this

    Pete

  • Jen

    This script does what I want, but…I want to replace a field that has a 0 in it with 190 instead. What happens is any field that already has 190, it's trying to put it in there again, and making it too long. How do I make this only replace a field that has just 0 in it opposed to a field that has any 0 in it?

  • JE

    Jen, you can add a WHERE clause to limit the replace.

    for example:

    UPDATE the_table SET the_column = REPLACE(the_column,'0','190') WHERE the_column LIKE '0';

  • Alfa

    Thanks for replace text in mysql guide.

  • ml

    I'm looking for a way to replace multiple values that are NOT LIKE 'x' so the REPLACE does not work for me.

    Does anyone know how to do it?

    Simon (September 1st, 2009 14:08) mentioned nesting…

  • Leyton Jay

    Sweeeeet! Cheers, worked a treat.

  • SMS

    To make my life easier, I was storing a date value in a text field. BUT, then I wanted to be able to increase the year by one. Can't do that for text. So, I wondered if I would have to run a loop in PHP with MySQL to get and replace each row one by one…bummer. Figured I would see what a search for MySQL replacing a string in a table would yield for me. PERFECT…found this post!! Thank you!

  • hieu

    I can't replace with path /, what must I do ?

  • Andy Killen

    Firstly, thanks for this, most useful.

    is there a way to incorporate REGEX into this so that a range of characters can be used in 'wildcard' type format?

  • Los Ang

    Yeah, but how do you take anything like a typo of a word and replace all instances of it, like aple to apple?

  • Helpful!! Thank you.

  • Vikram

    Thanks,
    It solved my problem.

  • John Hamelink

    Excellent, thank you! ๐Ÿ™‚

  • Jim

    Most helpful, thank you.

    One question, though. If you want to find a particular string and then remove it entirely, what do you set in the final setting (aka…‘replace found string with this string’). What goes in-between the ' ?

    Can you just leave it blank – essentially having a double ' ' ?

    Hopefully this question makes sense. ๐Ÿ™‚

    • Peter DeGregorio

      Jim — Yes two single quotes together will serve to remove the string. In example below I removed a non-printing character, hexadecimal 0D, from a column:

      update mytable set myfield=replace(myfield, x'0D', '')

  • Hoan Huynh

    Thanks for the examples.

  • Jan

    Great, this is what I needed ๐Ÿ™‚

    Thank you

  • adry

    How can I use replace in a string with ” caracter?

  • Dilip Shah

    Just what I was looking for… thanks a lot!

  • John

    hi i used this and it worked fine, but if i have two “Old Company” it changes both. How can I single out one of them by using something like WHERE to determain which one i want to change?

  • LV

    Any chance of explaining how to replace a single character? I have a column of prices (ie: $100) and want to remove the dollar sign.

    UPDATE products set price = replace(price, ‘$’, ”)

    isn’t working because it’s looking for values of “$” without the numbers.

  • German

    Excellent, tahnk you ๐Ÿ™‚

  • Wall Decals

    EXACTLY what I needed, thanks! Saved me a ton of work changing artisan names after making a filing mistake.

  • Waqas

    First of all thank you very much. This script save my lots of hours. Thanks Again….

  • Thanks for sharing this useful function but I think its not working to remove line break. Like
    replace ( Field , ‘\r\n’ ,” );

    • Its working. Actually we’ve to try with these also. ‘n’, ‘r’, ‘nr’.

      • Roger Bixley

        Actually, if you try it with “\n” and with “\r” then you won’t need to try it with “\n\r”.

  • Pingback: Zmiana fraz ze starej domeny na nowฤ… - ลšMIECI po zmianie domeny()

  • Kathleen

    This enabled me to take care of a task in about 10 minutes. Thanks much!

  • Ariel

    Thank you for it. I was searching your post.
    I have a think to say: “The best!”
    att Ariel

  • Micheal

    Hi,

    I got users with different email ids
    I have to replace all the email id after @…… with yahoo.com
    so can u help me?

  • Iza

    I need to change some name in database via PHP myadmin. Where to find this replace option?

  • Alper

    thanksssss

  • azhure

    saves me time to edit manually all the string i want to replace, glad you have this tutorial, thanks

  • vox

    Epic time saver…thanks!

  • seln

    thanks so much !!!!!!!!

  • ypiprem

    This was really helpful!

  • Can anyone answer the questions about using wildcard character updating records?
    Thanks in advance!

  • ravi

    hi sir/mam

  • ravi

    give me one solutions how to find the resent update column in mysql

  • Aleksandr Omelchenko

    Replace is really useful function it replaces occurrences of a specified string.

    By the way, we have gathered all MySQL string functions in one cheat sheet. You can find it here:

    http://mysqlbackupftp.com/freebies/MySQL_Cheat_Sheet_String_Functions.pdf

  • Ashish Trivedi

    how to replace ‘ in string , REPLACE(Name, ”’, ”)

Pin It on Pinterest

Share This

Share This

Share this post with your friends!