The Webmaster’s Weekly #12 is out & the Quick Tip tells you about a simple way of doing a search-replace in a MySQL table.
Well, it might be something new for Chrispian but I’ve been doing this for ages, infact I don’t know of any other search-replace technique for MySQL when you don’t need to replace the data of the whole column of a record, but just some text from between it, like in “hello world, today is wednesday” you want to replace “today is wednesday” with “yesterday was thursday”. This is ofcourse quite a simple example which can be done with a simple SQL. The tip however says that its quite simple to just download the data as SQL using phpMyAdmin, open it in a text editor, search-replace and then upload back after saving.
But being someone who’s done this way too many times & still does it, I wouldn’t recommend doing it to anyone unless the data you’ll be downloading is not too much and if there’s no easy way of doing it via an SQL UPDATE. The reason is why I prefer SQL in this case is because the data I work with, in which I’ve to perform search-replace, is always more than 10MB usually. So ofcourse downloading is a problem & then uploading is also a problem as I’ll have to split it into files each weighing less than 2MB(phpMyAdmin won’t upload files bigger than that, its the default max size allowed by PHP). 🙁 Quite a headache really!! :dizzy: On the otherhand, doing an SQL won’t take much time, and even writing the SQL would take up a bit of less time than downloading & uploading, and the additional benefit is that you get more fluent in SQL, so the next time it’ll be easier to do this. 😉
This is why, I wouldn’t recommend doing this download-search-replace-upload technique to anyone if using an SQL UPDATE is an option!! 😀
The swedish collation is default in MySQL4+, so quite possible that when the MySQL was updated on your server, it took that collation. I think you could’ve solved that by just changing the collation to french or general unicode via phpMyAdmin or MySQL’s CUI. 🙂
oh, thank you for the article! this saved me… hours. i updated links in all my (postnuke) articles in about 20 seconds. ::beer::
This is funny….
That doesn’t seem to work
It says something about me finding a bug in the parser engine…
Well, what version of MySQL are you using? I think that you need v4 & above for this.
I want to update the field data depending of value
in some other field in the same table using PHP script?
here’s my query
UPDATE ‘art1_article’ SET ‘article’=replace(‘article’,’rn’,”);
but I get a syntax error
the right syntax to use near ”art1_article’ SET ‘article’=replace(‘article’,’rn’,”)’
mysql version 4.1.19-standard
Is it the syntax or is that pesky rn I’m trying to remove out of the text field screwing up everything?
We’re talking about a 50+meg file here.
Don’t use the quotes on your field name:
UPDATE art1_article SET article=replace(article,’rn’,’’);
hello there,
i am trying to reproduce a data server that a company uses to present information to its customers via the web. No problem. What they are using right now is Microsoft Access, connecting via ODBC to a server on line. The cool thing that they can do is have several tables linked together. Like if a certain field is updated in one table, it updates the same info automatically in another table. So, i want to reproduce this in MySQL, but i can’t seem to find out how. Is there a way to pull this off on the server side ? or does that have to be done on the client side ?
thanks for any tips.
Is there a way to serch in one fields data and replace data in another field. I have a I have a field that has standard time like 7:30 PM which is fine for me but mysql won’t sort that easily. I would like create another field for 24 hour times that I can use for sorting. If possible do a find all 7:30 PM and replace with 19:30:00 in another field.
hey there is there some who can help me with update thing’s
i want to update data without delete or replacing the old data, i try it with my own but the result just the same, it come with new data instead the old and new data is been displaying….
the code just like this:
$results = mysql_query(“SELECT * FROM fujibb WHERE tt_id LIKE ‘%”. $query .”%’ ORDER BY tanggal DESC LIMIT $page, $limit”);
while ($data = mysql_fetch_array($results))
$namal = $data[“namap”];
if (!$namal == “”){
$bru=$namap.”,”;
$namap=$namal.$bru;
} }
$sql=”UPDATE fujibb SET namap=’$namap’WHERE tt_id=’$tt_id'”;
$result=mysql_query($sql);
….
i want the result like this: if the field is empty then insert new data but when the field is got a data then it will be added to the field..(sorry for my bad english i hope somebody can understand what i mean) thank’s
Hi, the search & replace features listed here are great and very useful… I have an extension of this concept, which is changing or deleting everything before or after the searched term or to change the whole string whenever there’s a partial match… For example, my ‘product name’ field has some names like:
‘Product A’
‘Product A with something else’
‘Product A with one thing’
or ‘A thing with Product A’
These come from various datafeeds, so I can’t edit the source.
What I’d ideally like to do is replace the whole field with ‘Product A’ whenever it’s found in the name, deleting all the other bits and pieces.
Is this a simple exercise, or a real palaver?
I’m always getting an ERROR when I input the syntax mentioned above.
Here is what I input:
UPDATE `jos_content` SET fulltext=replace(fulltext,’petcraze’,’petco’);
I receive this error:
There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem
ERROR: Unclosed quote @ 62
STR: ‘
SQL: UPDATE ‘jos_content’ SET fulltext=replace(fulltext,’petcraze’,’petco’)
I use MySQL version 4.1.21
Any suggestions?
I am trying to remove non-standard ascii characters in a medium-text field. The statement I have (which is not working) is:
update table set field=(replace(field, ‘P{ASCII}+’, ”));
I am working on MySQL 5.x.
Any help would be greatly appreciated.
Thanks in advance.
Donna, how about trying this:
[mysql]
UPDATE TABLE tablename SET field=REPLACE(field, ‘P{ASCII}+’, ”);
[/mysql]
You forgot the tablename in the statement above & there’s no need to enclose the REPLACE() function within brackets. And if it still doesn’t works then try this:
[mysql]
UPDATE TABLE tablename SET field=REPLACE(field, ‘^[x20-x7E]’, ”);
[/mysql]
And if that also doesn’t work then go the long way:
[mysql]
UPDATE TABLE tablename SET field=REPLACE(field, ‘^[A-Za-z 0-9 .,?'””!@#$%^&*()-_=+;:<>/|}{[]`~]*’, ”);
[/mysql]
Let me know which one worked for you! 🙂
how do i replace a comma (,) with a space???
[sql]UPDATE ‘character’ SET skills=replace(skills,’,’,’ ‘);[/sql]
I don’t think you need to escape the comma. Just put it in quotes as it is & the table name mustn’t be in quotes! 🙂
ok the second dilema 😀
i have a series of numbers in a field (ONE field)
1,2,3,4,5,6,7,8,9
I would like to add a 0 every third number, preferrably without the commas
1,2,0,3,4,0,5,6,0,7,8,0 to 1 2 0 3 4 0 5 6 0 7 8 0
how do i achieve this in the 2 steps of comma removal and 0 addition
ah, not sure. you’ll need to use RegEx for this now, google is your friend! 😉
I found that I had to modify the syntax slightly, like so:
UPDATE `contacts` SET `phone_other`=replace(`phone_other`,” “,”-“)
Thanks,
Ken
Awesome! This is just what I was looking for. I didn’t know if sql queries even had this capability. It certainly makes replacing a particular word in a column where each that field in each row is unique.
Thanks!
Hi,
I want to replace two different things in the same field, using the same replace function?
For example, I want 123_1-345_2 to become 123-345.
I could do
UPDATE TABLEE SET TABLEE.exp = Replace(exp,”_1″,””);
and then
UPDATE TABLEE SET TABLEE.exp = Replace(exp,”_2″,””);
BUT HOW CAN I DO IT IN THE SAME UPDATE QUERY?
Tahnks
If “123_1-345_2″ is consistent in all rows then you can just do this:
[mysql]
UPDATE TABLE SET exp = replace(exp,”123_1-345_2″,”123-345”);
[/mysql]
else you will have to execute two SQLs. Also, no need to prefix table to column name! 🙂
hello guys.
most of the codes posted are for changing data on the fields. is there a quick way in making a mysql query in changing table names(prefix)?
lets say my db has 50 tables with table names prefixed with “abc_” now i wish the whole 50 tables with prefix of “abc_” to be “123_”. any thoughts?
i do this with a text editor and find and replace, however it would be handy to have this query right on the phpmyadmin rather than opening another application; which in some case, doing a find and replace in the text file would result all “abc_” even on the content(which i only want the table names to be changed) areas would change to “123_”.
Yes there’s an easy way to rename tables
[sql]
ALTER TABLE `current_tablename` RENAME `new_tablename`;
[/sql]
Just use this SQL any number of times you want to quickly rename any number of tables you want. However I don’t think there’s any search and replace thing for this!! 🙂
Is there some way to know how many replaces there are done in a replace query? I would like to give a summary after a search&replace job.