Search code examples
regexoracle-databaseregexp-replace

Oracle regexp_replace numbers for letters

I think this should be simple but I can't seem to figure out a way to do it other than nesting regexp_replace. I want to replace each number with a corresponding letter something like:

regexp_replace(regexp_replace(regexp_replace('147','1','A'),'4','D'),'7','G')

result:

ADG

but with a list operator like this

regexp_replace('12345','[1234567890]','[ABCDEFGHIJ]')

but of course instead of ADG I get

[ABCDEFGHIJ][ABCDEFGHIJ][ABCDEFGHIJ][ABCDEFGHIJ][ABCDEFGHIJ]

Solution

  • You don't need regular expressions for this; you need the TRANSLATE function:

    select translate('147', '1234567890', 'ABCDEFGHIJ') as translated from dual;

TRANSLATED
-------------
ADG