MySQL substring replace, with simple query builder

Mar 3rd, 2010 at 11:58 am
filed under: Web Development / MySQL
Comments (3)

I hate the MySQL docs, for whatever reason, they always seem so much less intuitive than the PHP docs. Granted, I don't work with MySQL religiously, but I find I often need to search for the same thing again and again.

One of the things I use most lately is a substring replace, for example, all my meta keywords that had the term "snews"... I wanted them to be "sNews", note the caps.

I wanted a simple, quick way to globally replace all instances of "snews" with "sNews". Simple enough, it's not hard to do once you figure it out, but it's definitely not easy to figure out from the online docs for MySQL's string functions.

Essentially, here is the command you need to issue. You need the database table, field, the text you're searching for, and the text you want to change it too. Like this;

UPDATE [table]
    SET [field] = REPLACE ([field],'[text_to_find]','[text_to_replace]');

Now I know I'm going to use this one quite a bit, and I also needed an excuse to do a little jQuery :), so I wrote up a simple query builder for the substring replace. Just fill in the fields and click "Generate Query" and you're good to go.

*TIP: type naturally, the script will escape apostrophes for you.

Enter the name of your database table:

Enter the name of the field to perform the find & replace on:

Enter the text you'd like to replace:

Enter the text to replace with:

UPDATE [table]
    SET [field] = REPLACE ([field],'[text_to_find]','[text_to_replace]');

If you want to restrict the replacement, you simply add a WHERE clause, WHERE articleid = '15', etc. Any questions, feel free to leave a comment.

bookmark / share this: Bookmark and Share
rated 3/5 (3 votes)

MySQL, substring, replace

You may also be interested in:

3 comments

Add a new comment »

#1
sheetal sheetal said:
Apr 16th, 2010 at 9:31 am

Thanks. I got solution from this article.


#2
lim lim said:
Apr 20th, 2010 at 11:01 pm

thanks...how about if i have a data like asd040404 and i want to replace the last for digit '0404' to 0504..how should i do it?


#3
Matt Matt said:
Apr 21st, 2010 at 9:03 am

Hi Lim,

Well, if you know it's going to be asd040404, then you'd just use;

UPDATE [table]
    SET [field] = REPLACE ([field],'asd040404','asd040504');


If the beginning of the string is different and you know for a fact that there will be a space (or other character) after the last "0404", you could add the white space.

Something like;

UPDATE [table]
    SET [field] = REPLACE ([field],'0404 ','0504 ');


Otherwise, you'll need to do some deeper pattern matching, and I don't believe MySQL can currently capture matches with REGEXP.

At that point I would probably write a small PHP script to select all the rows containing the basic pattern, create the new data with PHP regular expressions and then do an update on each matching row.

Do you have any more examples of the pattern you're looking for?


Subscribe to comments feed

Write a comment

* = required field

:

:

:

:

You may insert urls in plain text, urls will be automatically linkified for trusted users and on seasoned posts only. All first comments are moderated, so use your email if you want to be remembered.


Back to top