phpMyAdmin – search and replace
# June 10, 2013 at 1:32 pm
not sure if this would belong here but giving it a shot.
This is likely a pretty easy query but I’m new to this so apologies as I learn and navigate my way around.
I was a table that have some entries that need updating.
I’ve done some research left and right and this is what I found as the solution:
UPDATE tablename SET tablefield = replace(tablefield,”findstring”,”replacestring”);
the issue i’ve been having is:
the 1st time I ran this, I needed to replace a value of “_2_” with “_some_word_”. When I ran this command, it replaced all appearances of “**2**” w/ “**0**”. so not only are my 2s now 0s but my **10s** are now **12s**.
what have I done wrong?? single quotes? doubles? no idea what to do other than manually change the cell values (drag).
as well, i can’t seem to find some proper docs on this. anyone have a resource? The main one doesn’t seem to discuss this. A few times I was told that i was using commands that were not part if my current version.
Thanks for you time.
cheers.# June 10, 2013 at 2:12 pm
>i can’t seem to find some proper docs on this.
[REPLACE() documentation](http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_replace). Pretty straightforward.
>so not only are my 2s now 0s but my 10s are now 12s
Even without the INT vs. VARCHAR issue, you’d still have messed-up data: instead of your `12`s becoming `10`s, they’d all be `1some_word`s.# June 10, 2013 at 6:15 pm
> my 2s now 0s but my 10s are now 12s
>> I got that backwards. my 0s turned to 2s, and 10s were now 12s
…really? what were the arguments you used (what were the values of _findstring_ and _replacestring_)?
>Was it a single vs double quotes error?
That particular problems was probably an INT vs. STRING error: trying to put a string into a INT column results in `0` (so, if you tried to replace _`2`_ with _`some_word`_, then all instances of `2` becoming `0` makes sense; but `0` becoming `2` does not. We need some clarification on this point).
To answer (what I _believe_ is) your larger question, the `2` in `12` **is** a `2`, so yes, it will be replaced with `some_word`; this **is** the correct behavior.
If it is not the _desired_ behavior, then you need to do something else. : ) You could write a user-defined function for it, or there are add-on packages like [`lib_mysqludf_preg`](https://github.com/mysqludf/lib_mysqludf_preg). You’d have to recompile, though.
You must be logged in to reply to this topic.