MySQL’s REPLACE() Is PHP’s STR_REPLACE()
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-FriendOver 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.
Discussion
Be Heard!
Share your thoughts with fellow developers of all skill levels! I want to hear from you!
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
interesting..I haven’t thought of using that to clean up some old code.
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?
I always back up the table before doing this, Jesus. Too much can be lost!
I have a problem when i use array to replace stuff, could you explain how its done the right way ?
Hex: Could you be more specific? Post a code example possibly?
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.
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!
@sinterklaas: A replace sounds like a good idea for that, though I would make a backup before trying it.
Wonderful! It works just fine even when used in the WHERE clause… thanks alot..
Thank you for this. now i need to change my str_replace(); functions into mysql replace() function. it is more quicker than doing same job in php. blogged this
it is better then using php’s str_replace() function. You dont need to create loops only you use replace() it is more faster.
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.
Thanks, exactly what I was looking for. I love blogs that help answer really simple questions with really simple answers.
thanks David, for REPLACE(content,’b>’,'strong>’);
@Tom: that’s a somewhat controversial statement. See the benchmark at http://net-beta.net/ubench/