1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL partial string replacement with wildcard unchanged

Discussion in 'General PHP and MySQL Discussions' started by Adam Howard, Nov 18, 2013.

  1. Adam Howard

    Adam Howard Well-Known Member

    It's been a while since I've needed to do this, so I've actually forgotten how. lol :ROFLMAO:

    In order to make this quicker, I'm trying to remember how to do a partial string replacement, with the combination of a wild-card that needs to remain unchanged.

    It's for an add-on that I'd like to update quickly, the table is xf_mood

    What I have is a bunch of entries like so

    styles/default/xenmoods/Angelic.png
    styles/default/xenmoods/Sick.png
    styles/default/xenmoods/Sad.png

    I need to change

    styles/default/xenmoods/

    to

    http://cdn.sociallyuncensored.com/styles/default/xenmoods/

    While still keeping the ending images unchanged.

    So the end result after running the command would be changing these

    styles/default/xenmoods/Angelic.png
    styles/default/xenmoods/Sick.png
    styles/default/xenmoods/Sad.png

    to this

    http://cdn.sociallyuncensored.com/styles/default/xenmoods/Angelic.png
    http://cdn.sociallyuncensored.com/styles/default/xenmoods/Sick.png
    http://cdn.sociallyuncensored.com/styles/default/xenmoods/Sad.png

    It's been a while since I've done this and I'm trying to remember how to do only a partial replacement string, with an unchanged wild card.
     
  2. AndyB

    AndyB Well-Known Member

    What's the name of the field?
     
  3. Jeremy

    Jeremy XenForo Moderator Staff Member

    Adam Howard likes this.
  4. Adam Howard

    Adam Howard Well-Known Member

    The database table is named: xf_mood
    The field is named: image_url

    Its so I can basically convert all the 300 photos from this add-on (link) to my CDN, without having to manually edit them all one by one (which I started doing, but then realized how much work that would be)
     
  5. Steve F

    Steve F Well-Known Member

    You could export the table, find & replace -> reimport
     
    Adam Howard likes this.
  6. Jeremy

    Jeremy XenForo Moderator Staff Member

    If you update the query in my previous post, it should work.
     
    Adam Howard likes this.
  7. AndyB

    AndyB Well-Known Member

    I would use this:

    Code:
    UPDATE xf_mood SET image_url = REPLACE(image_url, 'styles/default/xenmoods/', 'http://cdn.sociallyuncensored.com/styles/default/xenmoods/')
    
     
    Adam Howard likes this.
  8. Jeremy

    Jeremy XenForo Moderator Staff Member

    A CONCAT should be faster, since it doesn't have to do string manipulation + concatenation.
     
    Adam Howard likes this.
  9. Adam Howard

    Adam Howard Well-Known Member

    You know.... I never thought of that (though I should have).

    Thank you @Jeremy @Steve F and @AndyB (y)

    One of the things that make XenForo stand out so well is how many people are willing to help (as well as how quick you get replies with multiple solution). :cool:

    I'm going to link this thread in that thread for a point of reference should anyone else need it :)
     
    Shelley likes this.
  10. Adam Howard

    Adam Howard Well-Known Member

    Just because I know someone will forget or maybe someone who is new to SQL will see this and not know any better.

    You want to end those commands with ; at the end for good measure :)
     
  11. AndyB

    AndyB Well-Known Member

    The query I provided is to be used in phpMyAdmin where the semicolon is not used.
     
    SneakyDave likes this.
  12. Jeremy

    Jeremy XenForo Moderator Staff Member

    Semi-colons are not always required by mySQL.
     
    SneakyDave likes this.
  13. Adam Howard

    Adam Howard Well-Known Member

    phpMyAdmin said it was. :cautious:

    In fairness, I didn't think it would be either.
     
  14. Adam Howard

    Adam Howard Well-Known Member

    True. But in this case it was.
     
  15. AndyB

    AndyB Well-Known Member

    Interesting. Where does phpMyAdmin show this? Just curious and always wanting to learn.

    Thank you.
     
    SneakyDave likes this.

Share This Page