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'
On Wednesday, January 6, 2016 at 3:06:08 PM UTC+1, Ronnie Mueller wrote:
>
> 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
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
