0

On a WordPress site I want to replace " !" and " ?" by " !" and " ?". The challenge is that the replacement string not uses a common space char but a thin space char (U+2009).

PHPMyAdmin is used for database editing with this query:

UPDATE wp_posts SET post_content = REPLACE (post_content,' !',' !');

(Yes, it's the correct thin space char, but Courier is a fixed-width font not showing a difference.)

It seems that PHPMyAdmin is not correctly handling the special char, because simulating the query delivers zero hits.

But trying another replace query works, so the statement should be correct. Tables are using "utf8mb4_unicode_ci" as collation, which seems to be the default and correct setting of WordPress. Database server is MariaDB (10.1.26).

I'm not familiar enough how to specify Unicode chars in SQL queries. Can someone help me out there?

  • In SQL Server you could do this with a CHAR function. Like REPLACE(post_content, ' !', CHAR(0222)) would replace your bangs (!) with thorns (Þ ). I don't know of a place I can test this for MariaDB though. If that works, you would just need to know the integer value for the unicode char. – Jacob H Mar 7 '19 at 20:30
  • Thank you, that's what I looked for. :-) – Toby Mar 10 '19 at 15:24
0

Your UPDATE statement looks correct. Here's "proof":

mysql> SELECT HEX(REPLACE( " !", ' !',' !'));
+----------------------------------+
| HEX(REPLACE( " !", ' !',' !'))   |
+----------------------------------+
| E2808921                         |
+----------------------------------+

E28089 is the hex for the utf8 encoding for "THIN SPACE" (Unicode codepoint 8201=x2009).

And I suggest you do want utf8, not unicode.

You can nest the replaces:

UPDATE wp_posts SET post_content = 
        REPLACE(
        REPLACE(post_content,' !',' !'),
                             ' ?',' ?');

Or, for something wrong with the client (phpmyadmin),

UPDATE wp_posts SET post_content = 
        REPLACE(
        REPLACE(post_content,' !', UNHEX('E2808921')),
                             ' ?', UNHEX('E280893F'));

This assumes that post_content is declared CHARACTER SET utf8 or utf8mb4.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.