How to use the same equivalent of REGEXP_REPLACE(Oracle sql) in edb

Question

I am migrating from oracle sql to edb and wanted to convert the regular expression replace, but i am not able to bring all the constraints to the edb equivalent. Could you please help.

SET account_no = RTRIM (LTRIM (REGEXP_REPLACE (account_no, '[A-Y]', '', 1, 0, 'i'), ' '), ' ')

How do i bring the position(1), occurrence(0) and match_parameter('i') in edb. If i use the same i am getting the below error

ERROR: function regexp_replace(character varying, unknown, unknown, integer, integer, unknown) does not exist LINE 2: SET customer = RTRIM (LTRIM (REGEXP_REPLACE (customer, '... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 52

Answer 1

PostgreSQL's implementation of regexp_replace() is different from Oracle's. The function signature as mentioned in the documentation is regexp_replace(source, pattern, replacement [, flags ])

Calling regexp_replace(account_no,'[A-Y]','','gi') would be the equivalent of your desired invocation of regexp_replace(account_no,'[A-Y]','',1,0,'i')