| REGEXP_INSTR(’??b’, ’b’) | +--------------------------+ | 5 | +--------------------------+ 1 row in set (0.00 sec) mysql> SELECT REGEXP_INSTR(’ 㳟 bxxx’, ’b’, 4); +--------------------------------+ | REGEXP_INSTR(’??bxxx’, ’b’, 4) | +--------------------------------+ | 5 | +--------------------------------+ 1 row in set (0.00 sec) 

Characters within the Unicode Basic Multilingual Plane, which includes characters used by most modern languages, are safe in this regard: 

mysql> SELECT REGEXP_INSTR(’ бж b’, ’b’); +----------------------------+ | REGEXP_INSTR(’ бж b’, ’b’) | +----------------------------+ | 3 | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT REGEXP_INSTR(’ עב b’, ’b’); +----------------------------+ | REGEXP_INSTR(’ עב b’, ’b’) | +----------------------------+ | 3 | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT REGEXP_INSTR(’µå 周 ç б ’, ’ 周 ’); +------------------------------------+ | REGEXP_INSTR(’µå 周 ç б ’, ’ 周 ’) | +------------------------------------+ | 3 | +------------------------------------+ 1 row in set (0.00 sec) 

Emoji, such as the “sushi”  character 㳟  (U+1F363) used in the first two examples, are not included in the Basic Multilingual Plane, but rather in Unicode’s Supplementary Multilingual Plane. Another issue can arise with emoji and other 4-byte characters when REGEXP_SUBSTR()  or a similar function begins searching in the middle of a character. Each of the two statements in the following example starts from the second 2-byte position in the first argument. The first statement works on a string consisting solely of 2-byte (BMP) characters. The second statement contains 4-byte characters which are incorrectly interpreted in the result because the first two bytes are stripped off and so the remainder of the character data is misaligned. 

mysql> SELECT REGEXP_SUBSTR(’ 周周周周 ’, ’.*’, 2); +----------------------------------------+ | REGEXP_SUBSTR(’ 周周周周 ’, ’.*’, 2) | +----------------------------------------+ | 周周周 | +----------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT REGEXP_SUBSTR(’ 㳟 ’, ’.*’, 2); +--------------------------------+ | REGEXP_SUBSTR(’????’, ’.*’, 2) | +--------------------------------+ | ? 㳟揘㳟揘㳟揘 | 

