XF 2.2 proper way to update old ipb emoticons

[briansol]

I'm fairly certain this is from IPB conversion some 15 years ago... I have many posts that look like this in the db:

text text text [IMG]style_emoticons/<#EMO_DIR#>/hmm.gif[/IMG]
text text

I was thinking I could do a simple update replace like:

UPDATE xf_post SET message = REPLACE(message,'[IMG]style_emoticons/<#EMO_DIR#>/','[IMG]styles/default/xenforo/smilies/');

but, looking at a modern post with xf emoticons, the raw bbcode like

:)

is stored instead of

styles/default/xenforo/smilies/smile.gif

Looking a step further to make sure it's not just built in ones, i rebadged the ninja icon as

:ph34r:

from day one and it also shows as

:ph34r:

in the xf variant now too.

So, i assume the emoticons have all become part of the sprite sheet and show as SVGs instead of direct embedded the path into the xf_post.message field.

What's the proper way to update these posts? is replacing the path good enough as a patch? Or will that set me up for additional future failure when folder structures inevitably change again (xf3?)

Worse, my icon names don't always line up to their short code
eg

:shrug2:

is actually

shrug_n.gif

 

[Paul B]

Just enter the actual smilie code as the replacement string.

'UPDATE xf_post SET message = REPLACE(message, '[IMG]style_emoticons/<#EMO_DIR#>/hmm.gif[/IMG]', ':)');

Before:


After:

 

[briansol]

ok, that's what i was afraid of, and why i made a comment at the end about how they don't line up.
wondering if i can do a regex and find the top 20 instances to correct instead of all of them as it looks to be a manual effort here

 

[Paul B]

You can assign additional replacement text strings for smilies as required.

However, you would still need to enter the short code as the replace string in the query, not the actual image path.

If you are familiar with spreadsheets and functions, you could write a very quick function to parse the original URL into a replacement query for each one.

Whichever way you do it, it's going to require some sort of manual effort.

 

[briansol]

ugh, the more i dig into this the more crap i find...

<div align="right">[post=419906]Quoted post[/post]</div>

hard coded html in there from quotes too

 

[Paul B]

It's easy enough to remove all of those divs using two queries following the same approach.

 

[Paul B]

I did these one by one but Brogan mentioned a spreadsheet. Anyway here is the code for posts:

Those queries are for IPB I presume?

I suspect the author wants to update the content in XF.

 

[beerForo]

Those queries are for IPB I presume?

I suspect the author wants to update the content in XF.

Let me clarify, I did this BEFORE import in a test and it worked. Apologies for the misunderstanding unless you are still importing.

 

[beerForo]

I'm fairly certain this is from IPB conversion some 15 years ago...

I see, yes, and it is. I have deleted my post with the queries that are for before the import.

 

[briansol]

yeah, i imported ipb to vb in like 2005 and finally to xf1. these bad entries have been lurking for over 15 years now.

 

[briansol]

2 days of manual updates and i'm still finding junk to clean up.

Still haven't found a good way to build the regex to match the postid in the middle of the divs and replace it. i can't figure out how to use the normal $1 variables from regex matching in sql. <- rusty at this!