MySQL substring replace, with simple query builder

MySQL substring replace, with simple query builder

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.

Works for SQLite too!

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.

Tags

 

You might like

Comments


Thanks. I got solution from this article.


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?


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?


Thanks youuu ....


Awesome Query Generator. Really nice work.


Perfect example. I have been looking for about an hour until I found this example. I put kudos on wikispeedia.org


Excellent. Thanks a lot for this great solution


Excellent tool, but I have no idea what a field is. I want to replace text with another all through the whole database.

What do i stick in 'field name'


Hi Mark,

The field is the column name you'd like to perform the find & replace on.

For example, say you have a table for a simple blog called "Posts", and within that table there is an id column, a post_title column, a postdate column, and a post_content column (this contains the actual blog post content). The table would look something like this;

http://www.mdj.us/images/db-example.png

When you say the whole database, you're talking about multiple tables & columns like that, are you sure that's what you're looking for?

If I really needed to perform that global of a find & replace, I would probably write a PHP script to loop through all the fields I wanted to do a find & replace on.


Thanks Matt,

It is a great example I'm looking for!

Besides, if I want to adding a string at the end of each record for a table. What should I do on the SQL?

Thanks


Hi Ray,

You want to use concat.

UPDATE table SET field=concat(field,'string to append');


That should add your string to the end of each record. Totally untested however, on my phone watching the football game right now :-)


Thanks a lot, Matt!


Matt you are really the best :)


I found a simple tool to build mysql queries - dbForge MySQL Query Builder ( Http://www.devart.com/dbforge/mysql/queryb... ). It allows to "draw" mysql queries on the Query Diagram and it joins tables, so you don't need to type any character. But. I'm not sure whether it can create complex queries.

Comments are closed. No new comments allowed.

Copyleft 2002 - 2017 Matt Jones
Hand crafted with HTML5 & CSS3
↑ Back to top