Remove a character from a given position on Oracle

Question

Is there anyway to remove a character from a given position?

Let's say my word is: PANCAKES And I want to remove the 2nd letter (in this case, 'A'), so i want PNCAKES as my return.

Translate doesnt work for this. Replace doesnt work for this. Regex is damn complicated...

Ideas?

Answer 1

Example:

SUBSTR('PANCAKES', 0, INSTR('PANCAKES', 'A', 1, 1)-1) || SUBSTR('PANCAKES', INSTR('PANCAKES', 'A', 1, 1)+1)

I don't have an Oracle instance to test with, might have to tweak the -1/+1 to get the position correct.

References:

• INSTR
• SUBSTR
• Concatenating using pipes "||"