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.
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.
Comments
RSS Comments Feed
sheetal
lim
Matt
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?
gauz
Mohinish
Mike S
http://wiki.nisi.ro/2011/03/how-to-replace-substring-in-mysql-table-field/
jim
Visages
Mark Penny
What do i stick in 'field name'
Matt
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.
Ray
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
Matt
You want to use concat.
That should add your string to the end of each record. Totally untested however, on my phone watching the football game right now :-)
Ray