How to use Unicode chars on PHPMyAdmin?

Question

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?

Answer 1

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.