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

yes REPLACE and SUBSTR in the proper order will do the trick.

the end result should be a concatenation of the SUBSTR before the removed char to the SUBSTR after the char.

if the entire column is only one word, then you can just do an update, if the word is in another string, then you could use REPLACE as a wrapper.

Answer 2

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 "||"