#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    1
    Rep Power
    0

    Unhappy [Help] Update mysql table with replace function with regular expression


    Hi everybody!

    I have a table 'posts' with id,message fields. I want to update table 'posts'.

    In field 'message', I want to replace text 'httpdomain.com/threads/xxx#post123456' by 'httpdomain.com/posts/123456'

    How can I use SQL update with REPLACE function like below:

    update posts set message=REPLACE(message, 'httpdomain.com/*#post[\d]+', 'httpdomain.com/posts/$1');

    I try this, but it isn't correct

    If this isn't correct, please tell me how to update all post in table 'posts'

    Thank you very much
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    1
    Rep Power
    0
    You cannot do this with a simple query. The most obvious way is to select records one by one, replace contents and save the record back to the table. If you need to perform this operation every time a record is added it would be better to replace contents right before saving the record.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    you cannot do this with REGEX

    you can do it in pieces, but only if the xxx is always the same, or at least always the same length
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo