Oracle SQL regular expression replacement

Question

I have an Employees table where I have columns such as last_name first_name,salary, etc.. I want to write a query which will replace

• S or s to 'K'
• K or k to 'A'
• A or a to 'E'
• E or e to 'S'

for each last_name in the Employees table. Basically there are two ways to do it

1. translate: if I use this I am missing out either on lower case or upper case matching,e.g. translate(last_name,[skae],[kaes])

2. regexp_replace: I tried this, but from second replacement it is replacing the character with null. regexp_replace(last_name,'[(s|S)(k|K)(a|A)(e|E)]',[KAES],1,0,'i']

Answer 1

Use translate() for single character replacement:

select translate(last_name, 'SsKkAaEs', 'KKAAEESS')