Grow your CSS skills. Land your dream job.

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

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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".