Using REGEXP_REPLACE in Oracle

Question

Am trying to use backreference expression to take out three subexpressions from a search, and reposition each subexpression as follows:

 SELECT REGEXP_REPLACE('AAA 123 ppp','(^[[:alpha:]]+) ([[:digit:]]) ([[:alpha:]]+)','3円 1円 2円') from dual

However am getting the result as AAA 123 ppp where expecting ppp AAA 123 Where am i getting it wrong?

Answer 1

Missing + after [[:digit:]]

select regexp_replace('AAA 123 ppp','(^[[:alpha:]]+) ([[:digit:]]+) ([[:alpha:]]+)','3円 1円 2円') 
from dual

ppp AAA 123