Need a quick SQL query or two

[Caelum]

Heyas

Basically, I want to replace every mention of a phrase in my XF forum posts, profile comments, PCs, and so on to something else. Specifically, I want to change every occurrence of:
"http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/*"
To:
"http://smileys.mydomain.net/*"
With * being a wildcard of whatever smiley image comes after.

For what it's worth: pretty much all occurrences of this are wrapped in tags.

Basically, help me out here, which queries would I need to run on MySQL 5.1.52 to do this?
Cheers

 

[Mikey]

I'd start with:

SELECT * FROM xf_post WHERE message = 'http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/'

to see what comes up, make sure it's only what you need, then

UPDATE xf_post SET message 'http://smileys.mydomain.net/' WHERE mesage = 'http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/'

untested!

Edit: if = brings up no results, try "LIKE"

so,

UPDATE xf_post SET message 'http://smileys.mydomain.net/' WHERE mesage LIKE '%http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/%'

 

[Caelum]

Thanks for the response

No luck so far; selecting with both = and LIKE turned up empty.

 

[Jake Bunce]

A string replacement query should work for your purposes:

UPDATE xf_post
SET message = REPLACE(message, 'http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/', 'http://smileys.mydomain.net/')

Backup first.

You will need to run the same query on other tables if you want to affect other things like profile posts and personal conversations:

UPDATE xf_profile_post
SET message = REPLACE(message, 'http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/', 'http://smileys.mydomain.net/')

UPDATE xf_profile_post_comment
SET message = REPLACE(message, 'http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/', 'http://smileys.mydomain.net/')

UPDATE xf_conversation_message
SET message = REPLACE(message, 'http://fs1.mydomain.net/public/style_emoticons/<#EMO_DIR#>/', 'http://smileys.mydomain.net/')

 

[Caelum]

That one did the trick.

Thanks a bunch.