- This topic is empty.
June 10, 2013 at 1:32 pm #45431
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 1:45 pm #138214
ZING. I may have just answered my own q?
i’m trying to change an INT to a VARCHAR in a INT column. likely the issue – _I hope._
** edit **
that was the issue. cheers.June 10, 2013 at 2:12 pm #138218__Participant
>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 3:40 pm #138226
> so not only are my 2s now 0s but my 10s are now 12s
Sorry. I got that backwards. my 0s turned to 2s, and 10s were now 12s.
so where did I go wrong w/ the command? Was it a single vs double quotes error?June 10, 2013 at 6:15 pm #138244__Participant
> 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.
- The forum ‘Back End’ is closed to new topics and replies.