Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Posts
    4

    Unanswered: Replace portions of text fields

    I am trying to devise a query tht will replace multiple instances of a text string with another within the same table. Every query I give it tells me that it is invalid in some fashion.

    For Instance, in the POST field, I set a test string of The text qazwsxedc should be replaced with qwerty. I want to replace the text qazwsxedc with qwerty across multiple records.

    Can this be done via the command line or do I need to go in and manually edit 4000 records in my database?
    Last edited by rjordan; 12-06-02 at 09:54.

  2. #2
    Join Date
    Dec 2002
    Posts
    2
    I'm no expert, but I don't think you can do this without subselects or an outside programming language.

    If so, a ten-minute throwaway perl script is perfect for this problem, because it sounds like you just want it done, not solved.

  3. #3
    Join Date
    Dec 2002
    Posts
    4
    You are right about wanting to just get it done. Unfortunately, I do not know enough about Perl or MySQL to do it.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    update yourtable
    set post = replace(post,'qazwsxedc','qwerty')
    where post like '%qazwsxedc%'

    the WHERE clause is so that you don't needlessly update every row in the table


    rudy
    http://rudy.ca/

  5. #5
    Join Date
    Dec 2002
    Posts
    4
    I thank you for your assistance. Let me make sure I understand before I attempt this...

    This should replace all instances of qazwsxedc in every record in the POST column without affecting the surrounding text.

    For instance:

    The text qazwsxedc should be replaced with qwerty.

    will be changed to read:

    The text qwerty should be replaced with qwerty.

    Is that correct?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yup, that's the way it's supposed to work

    don't forget the WHERE clause -- there's no point in updating a column value with itself (if it doesn't contain the search string)


    rudy

  7. #7
    Join Date
    Dec 2002
    Posts
    4
    Thank you SO MUCH! That did it!

Posting Permissions

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