Empty strings in REGEXP_REPLACE

[Ronnie Mueller]

Hi,

We are migrating from Oracle to H2 database and came across the following compatibility issue.

The built in function REGEXP_REPLACE behaves differently in Oracle and H2 when we try to replace a string with an empty string.

 

Example:

select REGEXP_REPLACE('Hello    World', ' +', '') from dual;

-> Oracle returns HelloWorld but H2 returns null.

As per H2 documentation the REGEXP_REPLACE returns null if one of the parameters is null.

Is this behaviour intentional or is this a bug? I think it is a valid use case to replace a match with an empty string.

Are there any workarounds available?

 

We are using H2 version 1.4.190 and Mode=Oracle

 

I’ve also tested the behaviours for the following statements:

select REGEXP_REPLACE('Hello    World', ' +', NULL) from dual;

-> Oracle returns HelloWorld

-> H2 returns null

 

select REGEXP_REPLACE('Hello    World', '', 'a') from dual;

-> Oracle returns 'Hello    World'

-> H2 returns null

 

select REGEXP_REPLACE('Hello    World', NULL , 'a') from dual;

-> Oracle returns 'Hello    World'

-> H2 returns null

 

select REGEXP_REPLACE('', ' +', 'a') from dual;

-> Oracle returns NULL

-> H2 returns NULL

 

Thanks,

Ronnie

[Noel Grandin]

In other places Oracle converts empty strings to null. So what is
happening here is that we apply the Oracle emulation's "empty string
to null" logic, and then the REGEXP_REPLACE does it's thing.

We'd need to include special logic in the REGEXP_REPLACE function to
cope with this in Oracle mode.

Patches are welcome :-)

[Joachim Lous]

Workaround: 

   Capture the character following the string to remove, and replace then both with the capture. Make the capture optional to match trailing occurrences too.

Doesn't fix the incompatibility, but solved my use case of stripping all emebedded whitespace:

   select regexp_replace('    space    less   ', '\s+(.)?', '$1');

   -> 'spaceless'

[Joachim Lous]

Same issue here. 

It seems that H2 converts to null already on evaluating the function, while Oracle does it later (on write?).

Does this go for functions in general, or is does Oracle treat 'replace' differently in order to enable string filtering?

Is there any other way to make H2 filter particular characters from any position in a string?

[Joachim Lous]

simplification: use an empty capture:
regexp_replace('space    less', '\s+()', '$1');

-> spaceless