Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Feb 2009
    Thanked 0 Times in 0 Posts

    Replace in MYSQL

    I am using the syntax: REPLACE(str,from_str,to_str)

    to replace data in my table. However I have a slight issue I would like to address, when I have the following records

    jon | jonathon | jonas | jonny

    and I do the replace with

    REPLACE(names, jon, '')

    I am left with

    | athon | as | ny

    Is there a way that it can only replaces 'whole words' and not replace the string where it is part of a larger string?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,738 Times in 4,700 Posts
    UPDATE table SET name = Replace( name, 'jon', '' ) WHERE name = 'jon';

    If you REALLY have records with delimited text strings in them (that is, if you really have a field called "names" that really has "jon | jonas" in it), then it looks like it's time to redesign the DB. Delimited text strings are, in general, "poison" in relational databases.


    If you *KNOW* you have (for example) a space on each side of EVERY delimiter, then you can probably do this in 3 steps:

    (1) put a delimiter on both front and rear of all values:
    UPDATE table SET names = '| ' + names + ' |'

    (2) now do the replace using delimiters:
    UPDATE table SET name = Replace( names, '| jon |', '|' )

    (3) Strip off the front and rear delimiters
    UPDATE table SET name = Substring(names,2,Len(names)-4)
    [not sure of syntax of the for MySQL, but you get the idea]

    You can, with care, combine steps 1 and 2. For step 3, you need to make sure that the length is indeed > 4 (you might have collapsed '| jon |' into just '|' if it was only name in the string).

    See why a DB redesign would be a good idea?


    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts