CODEWITHSUNDEEP
regexoracle-databaseregexp-replace
Mark Frampton
Mark Frampton

Reputation: 3

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]

Upvotes: 0

Views: 584

Answers (2)

Nick Reed
Nick Reed

Reputation: 5059

This can't be easily done with REGEXP_REPLACE.

Regex doesn't really have the power to decide what to replace based on what it matches; it's usually only meant to make a match and leave the rest up to whatever programming language you're using. You might be able to swing this in something like Python, but as has been pointed out in another answer, for Oracle, REGEXP_REPLACE is the wrong tool for the job.

Upvotes: 1

user5683823
user5683823

Reputation:

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

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

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

Upvotes: 2

Related Questions