[FIXED] Compatibility regexp_replace() postgres and h2

Issue

We have Spring Boot Application, and native sql in Postgres

Select .... 
where regexp_replace(LOWER(company_name), '\s', '', 'g') = regexp_replace(LOWER(:suspect), '\s', '', 'g')

And have tests H2 databases, where regexp_replace flag 'g' not supported.

Solution

I am not sure that you need the g flag in the case of H2, because global replacement of all matches appears to be the default. From the documentation:

Replaces each substring that matches a regular expression. For details, see the Java String.replaceAll() method. If any parameter is null (except optional flagsString parameter), the result is null.

I am assuming that H2's REGEXP_REPLACE is being implemented under the hood by Java String#replaceAll. In this case, all matching substrings will automatically be replaced.

Regarding your problem with two different databases, you really should try to use the same database for both testing and production. That failing, you might want to stick to ANSI SQL if possible.

Answered By - Tim Biegeleisen
Answer Checked By - Cary Denson (JavaFixing Admin)