SQL partial string replacement with wildcard unchanged

[Adam Howard]

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

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.

 

[AndyB]

What's the name of the field?

 

[Jeremy]

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

UPDATE xf_moods SET path = CONCAT('http://cdn.sociallyuncensored.eu', path)

Obviously, updating the field and table properly. As always, back up your database.

 

[Adam Howard]

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)

 

[Steve F]

You could export the table, find & replace -> reimport

 

[Jeremy]

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]

I would use this:

UPDATE xf_mood SET image_url = REPLACE(image_url, 'styles/default/xenmoods/', 'http://cdn.sociallyuncensored.com/styles/default/xenmoods/')

 

[Jeremy]

A CONCAT should be faster, since it doesn't have to do string manipulation + concatenation.

 

[Adam Howard]

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

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).

I'm going to link this thread in that thread for a point of reference should anyone else need it

 

[Adam Howard]

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

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:

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

 

[AndyB]

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

The query I provided is to be used in phpMyAdmin where the semicolon is not used.

 

[Jeremy]

Semi-colons are not always required by mySQL.

 

[Adam Howard]

The query I provided is to be used in phpMyAdmin where the semicolon is not used.

phpMyAdmin said it was.

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

 

[Adam Howard]

Semi-colons are not always required by mySQL.

True. But in this case it was.

 

[AndyB]

phpMyAdmin said it was.

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

Thank you.