MySQL’s REPLACE() Is PHP’s STR_REPLACE()

By  on  

The PHP function that I use the most could be str_replace(). If you aren't familiar with the function, here's what it does:

$string = 'Hello_My_Friend';
$result = str_replace('_', '-', $string);  //becomes Hello-My-Friend

Over the past months, I've grown to realize that I oftentimes use the function as a quick fix. I've been using MySQL's version of str_replace() a lot lately in an effort to cleanse the sins of my programming past. Here's a basic usage of MySQL's REPLACE function:

UPDATE website_content_pages SET content = REPLACE(content, 'b>', 'strong>');

The above code changes all <B> and </B> tags to <STRONG> and </STRONG> tags. Using MySQL's function allows me to fix the problem once instead of "re-fixing" it every time the page loads.

Recent Features

  • By
    Create a Sheen Logo Effect with CSS

    I was inspired when I first saw Addy Osmani's original ShineTime blog post.  The hover sheen effect is simple but awesome.  When I started my blog redesign, I really wanted to use a sheen effect with my logo.  Using two HTML elements and...

  • By
    Create a CSS Cube

    CSS cubes really showcase what CSS has become over the years, evolving from simple color and dimension directives to a language capable of creating deep, creative visuals.  Add animation and you've got something really neat.  Unfortunately each CSS cube tutorial I've read is a bit...

Incredible Demos

  • By
    Animated AJAX Record Deletion Using MooTools

    I'm a huge fan of WordPress' method of individual article deletion. You click the delete link, the menu item animates red, and the item disappears. Here's how to achieve that functionality with MooTools JavaScript. The PHP - Content & Header The following snippet goes at the...

  • By
    MooTools dwCheckboxes Plugin

    Update / Fix: The checkboxes will no longer toggle when the "mouseup" event doesn't occur on a checkbox. Every morning I wake up to a bunch of emails in my Gmail inbox that I delete without reading. I end up clicking so many damn checkboxes...

Discussion

  1. Tom

    Bit of an aside but just for everyone’s info, don’t forget that str_replace is pretty quick but strtr (string translate) is even faster.

    To use the same example:

    $string = 'Hello_My_Friend';
    $result = strtr($string, '_', '-');  //becomes Hello-My-Friend
    
  2. Jay

    interesting..I haven’t thought of using that to clean up some old code.

  3. Thats a big coincidence. I was going to replace an image link used on some content pages as well and I was going to use the same function to do it. Besides testing it on a test database or doing a db back up or something is there a safe way making sure that this doesnt mess up the table?

  4. I always back up the table before doing this, Jesus. Too much can be lost!

  5. Hex

    I have a problem when i use array to replace stuff, could you explain how its done the right way ?

  6. Hex: Could you be more specific? Post a code example possibly?

  7. This perhaps is useful to some, then again what about if you replace text in a table when its not meant to be replaced? It could of been a letter directing to something else. So in your example this would get replaced also:

    dumb>some msg => dumstrong>some msg

    Makes no since to use REPLACE for them matters.

    • none

      Anyone who stores their presentation code in a database without differentiating the tags from the text using HTMLENTITIES() deserves to have this happen.

      You, sir, deserve what you get.

  8. Hi, I have a question. At our website people can post poems on our website. In dutch poems peolpe use a lot of > ‘ . But our website doesn’t seem to proces it. We get an error can we fix this by replacing it whit a rewrite?? Thanks Already!

    • The PHP function htmlentities() will make the > into a html entity that your browser wont parse into an element.

  9. @sinterklaas: A replace sounds like a good idea for that, though I would make a backup before trying it.

  10. jacobain

    Wonderful! It works just fine even when used in the WHERE clause… thanks alot..

  11. it is better then using php’s str_replace() function. You don’t need to create loops only you use replace() it is more faster.

  12. no need to backup..
    you simply

    create temporary table foo select * from bar;
    do stuff with foo;
    select * from foo;

    when its done foo disapears.. now if u like what u see you comment outter lines.. keeping the
    do stuff with foo.

  13. Garrett Griffin

    Thanks, exactly what I was looking for. I love blogs that help answer really simple questions with really simple answers.

  14. thanks David, for REPLACE(content,'b>','strong>');

  15. @Tom: that’s a somewhat controversial statement. See the benchmark at http://net-beta.net/ubench/

  16. coder

    Amazing how much people don’t know about MySQL built in functions.

  17. I have been using this for a while but I cannot get the syntax right with the WHERE included

    UPDATE [tbl]
    SET [field1] = REPLACE(field1, ‘foo’ , ‘foo2’ )
    WHERE ( field2 like ‘foo3’)

  18. Yes. Sometime, replace is a very good solution.

    First we need to understand that we need to replace the value in database or we need to replace the value in front end only.

    If we need to replace the value in database, then we have to use replace command of mysql. But if we want to replace the value for front end only, then we can use only str_replace of php (if we are using php script).

  19. why if replace 2?
    REPLACE(content,’b>’,’strong>’);
    and REPLACE(content,’i>’,’b>’);

    in one code.

    • J. Cooper

      You would nest them into one…

      REPLACE(REPLACE(content,’i>’,’b>’),’b>’,’strong>’);
      
  20. @DJIEXTRAY
    I think it just an example.

    @davidwalsh
    Great tutorial! thx!

  21. Doug Thwaites

    Thanks, Thanks and uh… Thanks. Ur awesome that twice this month you blog has give me some good code/ideas.

    Cheers

  22. Tal

    Love your blog always helpful!

  23. Is there any way to perform it case insensitive? btw thanks for example

  24. Dan

    let’s try that with escaping…

    <b class=”highlight”>some text</b>

    becomes

    <b class=”highlight”>some text</strong>

Wrap your code in <pre class="{language}"></pre> tags, link to a GitHub gist, JSFiddle fiddle, or CodePen pen to embed!