simplification: use an empty capture:
regexp_replace('space less', '\s+()', '$1');
-> spaceless
On Wednesday, August 23, 2017 at 10:19:30 AM UTC+2, Joachim Lous wrote:
>
> 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.
