HHispañola

# 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.

HHispañola

# June 10, 2013 at 1:45 pm

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._

thx

** edit **

that was the issue. 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.

HHispañola

# June 10, 2013 at 3:40 pm

Thanks traq.

> 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

> 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.