Finding substring that starts with ':' until the first space after the ':' character

Question

I want to find a substring that starts with : and ends with ", and then replace all occurrences with dd/mm/yyyy.

How is this possible using oracle regexp_replace?

For example, to_date('13/09/2016',:"SYS_B_08") should become to_date('13/09/2016','dd/mm/yyyy').

Thank you. Best regards.

;)

Answer 1

Something like this?

select regexp_replace('to_date(''13/09/2016'',:"SYS_B_08")', ':"[^"]*"', 
                                                '''dd/mm/yyyy''') as new_str from dual;


NEW_STR                          
----------------------------------
to_date('13/09/2016','dd/mm/yyyy')