Jump to content

Help with SLQ Query to replace emoji


PPlanet
 Share

Recommended Posts

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!

Link to comment
Share on other sites

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 by PPlanet
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...