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
translate: if I use this I am missing out either on lower case or upper case matching,e.g.
translate(last_name,[skae],[kaes])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']
Advertisement
Answer
Use translate() for single character replacement:
select translate(last_name, 'SsKkAaEs', 'KKAAEESS')