Unicode escape sequence at MySQL command line

Question

Short version:

What escape sequence can I use to search for Unicode characters in mysql command line?

Long version:

I'm looking for a way to search a column for records containing a unicode sequence, U + 200B, in mysql from the command line. I can't figure out which kind of escape to use. I've tried \ u200B and x200B and even I finally found one blog that suggested the _utf8 syntax. This will produce the character on the command line:

select _utf8 x'200B'; 

Now I'm stuck trying to get this to work in a "LIKE" request.

This generates characters, but% seems to lose its special meaning when placed in the LIKE part:

 select _utf8 x'0025200B0025'; 

I also tried concat, but it didn't work either:

 select concat('%', _utf8 x'200B', '%'); 

Additional information:

I have some data that contains space characters (zwsp), Unicode Point U + 200B in it. This is usually caused by copying / pasting from websites that use zwsp in their release. With most Unicode characters, I can simply insert the character into the terminal (or create it using the key code), but since it is invisible, it is a little more complicated. I can create a file that generates the sequence "%%", and copy / paste it into the terminal, and it will work, but it leaves my command history and output to the hard drive. I would think that there is an easy way to do this in MySQL, but so far I have lagged a bit.

Thanks in advance,

-Paul Burney

Answer 1

 select _utf8 x'0025200B0025'; 

This is not UTF-8, this is UTF-16 / UCS-2. You could say SELECT _ucs2 0x0025200B0025 if you have UCS-2 support in your copy of MySQL.

Otherwise, the character encoding of the sequence of bytes U + 200B in UTF-8 will be 0xE2, 0x80, 0x8B:

 select 0xE2808B; 

Answer 2

If it is Linux, then hold Ctrl + Shift + U, then release U and enter 200B.