• 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

Adam Howard

Well-known member
#1
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.
 

Adam Howard

Well-known member
#4
What's the name of the field?
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)
 

Jeremy

Well-known member
#6
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)
If you update the query in my previous post, it should work.
 

AndyB

Well-known member
#7
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

Well-known member
#9
You could export the table, find & replace -> reimport
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 :)
 

Adam Howard

Well-known member
#10
Use CONCAT:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat

Code:
UPDATE xf_moods SET path = CONCAT('http://cdn.sociallyuncensored.eu', path)
Obviously, updating the field and table properly. As always, back up your database.
I would use this:

Code:
UPDATE xf_mood SET image_url = REPLACE(image_url, 'styles/default/xenmoods/', 'http://cdn.sociallyuncensored.com/styles/default/xenmoods/')
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 :)