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

richyen · Accepted Answer · 2021-06-01 06:51:50Z

0

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')

answered Jun 1, 2021 at 6:51

richyen

10.3k4 gold badges18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

praveen kumar Over a year ago

Thank you. it worked and also thanks for the explanation.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related