Regex replace with empty string results in NULL characters in between

Question

Replacing a character with regexp_replace results in NUL characters in between.

SELECT REGEXP_REPLACE(aColumn,'c', '')
INTO OUTFILE 'Replaced.csv';

for abcd results in:

ab d (infact: abNULd)

This does not occur with just REPLACE

using MySQL 8

For now the workaround is: an additional REPLACE of CHAR(0) with '' — Ujjwal Singh
– Ujjwal Singh, Commented Feb 5, 2019 at 1:22

Rick James · Accepted Answer · 2019-02-05 04:40:05Z

2

Ugh! The plot thickens:

mysql> SELECT REGEXP_REPLACE('abcd', 'c', '') = 'abd';
+-----------------------------------------+
| REGEXP_REPLACE('abcd', 'c', '') = 'abd' |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

mysql> SELECT HEX(REGEXP_REPLACE('abcd', 'c', '')), HEX('abd');
+--------------------------------------+------------+
| HEX(REGEXP_REPLACE('abcd', 'c', '')) | HEX('abd') |
+--------------------------------------+------------+
| 610062006400                         | 616264     |
+--------------------------------------+------------+

mysql> SELECT LENGTH(REGEXP_REPLACE('abcd', 'c', '')), LENGTH('abd');
+-----------------------------------------+---------------+
| LENGTH(REGEXP_REPLACE('abcd', 'c', '')) | LENGTH('abd') |
+-----------------------------------------+---------------+
|                                       6 |             3 |
+-----------------------------------------+---------------+

mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.15    |
+-----------+

Looks like there are NULs all over! And looks like they are ignored--somewhat!

I filed http://bugs.mysql.com/94203

MariaDB 10.3.11 "does the right thing" ( 1 / no NULs / 3,3 )

edited Feb 5, 2019 at 4:40

answered Feb 5, 2019 at 4:29

Rick James

80.8k5 gold badges52 silver badges119 bronze badges

1

Regular Expressions MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. (Prior to MySQL 8.0.4, MySQL used Henry Spencer's implementation of regular expressions, which operates in byte-wise fashion and is not multibyte safe. Try to wrap REGEXP_REPLACE with CONVERT() using proper charset, for example, SELECT HEX(CONVERT(REGEXP_REPLACE('abcd', 'c', '') USING latin1)), HEX('abd');.

Akina
– Akina

2019-02-05 05:09:50 +00:00
Commented Feb 5, 2019 at 5:09
1

@Akina - Then my gripe is that it converts to ucs2 (I guess) without converting back. I don't have the energy tonight to come up with test cases that demonstrate that and/or that it makes any difference.

Rick James
– Rick James

2019-02-05 05:16:57 +00:00
Commented Feb 5, 2019 at 5:16
@RickJames You have laid it out much more neatly than I would have

Ujjwal Singh
– Ujjwal Singh

2019-02-05 21:16:37 +00:00
Commented Feb 5, 2019 at 21:16

Add a comment |

Stack Exchange Network

Regex replace with empty string results in NULL characters in between

1 Answer 1

Your Answer

Hot Network Questions

Regex replace with empty string results in NULL characters in between

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions