Unicode (hexadecimal) character literals in MySQL

Question

Is there a way to specify Unicode character characters in MySQL?

I want to replace the Unicode character with an Ascii character, something like the following:

Update MyTbl Set MyFld = Replace(MyFld, "ẏ", "y") 

But I use even more obscure characters that are not available in most fonts, so I want to be able to use Unicode character characters, something like

 Update MyTbl Set MyFld = Replace(MyFld, "\u1e8f", "y") 

This SQL statement is called from a PHP script - the first form is not only unreadable, but also does not work!

Answer 1

Thank you for your suggestions, but I think the problem has returned to the system again.

There are many levels to reveal, but as far as I can tell (on this server, at least) the command

 set names utf8 

makes utf-8 processing correct, whereas

 set character set utf8 

not.

In my environment, they are called from PHP using PDO, which may be the difference.

Thanks anyway!

Answer 2

You can specify hexadecimal literals (or even binary literals ) with 0x , x'' or x'' :

 select 0xC2A2; select x'C2A2'; select X'C2A2'; 

But know that the return type is a binary string, so every byte is considered a character. You can check this with char_length :

 select char_length(0xC2A2) 

2

If you want instead of UTF-8 , you need to use convert :

 select convert(0xC2A2 using utf8mb4) 

And we can see that C2 A2 is considered 1 character in UTF-8:

 select char_length(convert(0xC2A2 using utf8mb4)) 

1

---

In addition, you do not need to worry about invalid bytes, because convert will automatically delete them:

 select char_length(convert(0xC1A2 using utf8mb4)) 

0

As you can see, pin 0 , because C1 A2 is an invalid UTF-8 byte sequence.

Answer 3

You can use hex and unhex , for example:

 update mytable set myfield = unhex(replace(hex(myfield),'C383','C3')) 

Answer 4

The syntax of the MySQL string is listed here , as you can see, there are no conditions for numeric escape sequences.

However, since you are embedding SQL in PHP, you can calculate the correct bytes in PHP. Make sure the bytes you put in SQL really match your client character set .

Answer 5

There is also a char function that allows you to do what you want (by providing byte numbers and an encoding name) and get a char.