Help with SLQ Query to replace emoji

[PPlanet]

Hello everyone. A few months ago my site was migrated to another server, and the emojis were corrupted as they used a mysql dump with the wrong character set. Anyway, not much that I can do about that now, but I would like to clean up the traces that now simply show as a question mark: ? (New emojis from the migration date onwards show fine though). But, the result is that I have 2 years of content with question marks where they shouldn't be, and this could confuse readers.

The code of the corrupted emojis is:

<img alt="?" class="ipsEmoji" src="https://twemoji.maxcdn.com/2/72x72/3f.png">

That emoji does not exist, and I would like to use a query to either clean it up, or if that's not possible (or too risky) to replace it with an image file hosted on my forum. I could use a transparent png or gif file of 1 x 1 pixel, which would have almost the same effect of showing nothing instead of the question marks. And call the file pixel.png for example.

Or if I'd only be able to replace it with another emoji, for example https://twemoji.maxcdn.com/2/72x72/1f642.png

(I'd prefer my own transparent pixel file though, as no emoji would suit all cases unless you know of a transparent one)

Anyway, I'll appreciate any help with this. Cheers!

[bfarber]

UPDATE forums_posts SET post=REPLACE( post, 'what to search for', 'what to replace with' );

 

[PPlanet]

1 hour ago, bfarber said:

UPDATE forums_posts SET post=REPLACE( post, 'what to search for', 'what to replace with' );

 

Thanks @bfarber

So for example to replace it with an image:

UPDATE forums_posts SET post=REPLACE( post, '<img alt="?" class="ipsEmoji" src="https://twemoji.maxcdn.com/2/72x72/3f.png">', '<img alt="alt text" class="ipsImage" src="https://forums.mysite.com/uploads/pixel.png">' );

Or for replacing it with nothing like below?

UPDATE forums_posts SET post=REPLACE( post, '<img alt="?" class="ipsEmoji" src="https://twemoji.maxcdn.com/2/72x72/3f.png">', '' );

 

Edited October 26, 2020 by PPlanet

[IPCommerceFan]

That should work, however I would verify by testing on a single post first.

After REPLACE(), add WHERE pid = 1234;   (post ID of a post that contains the emoji you're replacing)

[PPlanet]

6 hours ago, IPCommerceFan said:

That should work, however I would verify by testing on a single post first.

After REPLACE(), add WHERE pid = 1234;   (post ID of a post that contains the emoji you're replacing)

Will do, and will do on my test site first too. Cheers!

[PPlanet]

Hmm, I've tried all the options and it remains unaffected:

Not sure what I'm missing. Any ideas?

[PPlanet]

Actually, it works :) What I saw as

<img alt="?" class="ipsEmoji" src="https://twemoji.maxcdn.com/2/72x72/3f.png">

In the database it was showing only as

<span class="ipsEmoji">?</span>

So the query that works is

UPDATE forums_posts SET post=REPLACE( post, '<span class="ipsEmoji">?</span>', '' );

Cheers