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?

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

Solution 2

You should strongly consider using regexp_replace. It is shorter and not so complicated as it seems at a first glance:

SELECT REGEXP_REPLACE( S, '^(.{1}).', '\1' )
FROM (
  SELECT 'PANCAKES'
  FROM DUAL
)

The pattern ^(.{1}). searches from the start of the string ( denoted by ^ ) for exactly one ( .{1} ) of printable or uprintable characters followed by again just one of those characters ( . ). The "exact" part is closed in parenthesis so it can be referenced as match group by it's number in the third function's argument ( \1 ). So the whole substring matched by regexp is 'PA', but we reference only 'P'. The rest of the string remains untouched. So the result is 'PNCAKES'.

If you want to remove N-th character from the string just replace number 'one' in the pattern (used to remove second character) with the value of N-1.

It's good for programmer or any kind of IT specialist to get familiar with regular expressions as it gives him or her a lot of power to work with text entries.

Solution 3

Or use a custom made SplitAtPos function using SUBSTR. Advantage is that it still works on Oracle v9.

set serveroutput on
declare

s1 varchar2(1000);
s2 varchar2(1000);

function SplitAtPos(s in out varchar2, idx pls_integer)
    return varchar2
is
    s2 varchar2(1000);
begin
    s2:=substr(s,1,idx-1);
    s:=substr(s,idx,length(s)-idx+1);
    return s2;
end;

begin
s1:='Test123';
s2:=SplitAtPos(s1,1);
dbms_output.put_line('s1='||s1||' s2='||s2);

s1:='Test123';
s2:=SplitAtPos(s1,5);
dbms_output.put_line('s1='||s1||' s2='||s2);

s1:='Test123';
s2:=SplitAtPos(s1,7);
dbms_output.put_line('s1='||s1||' s2='||s2);

s1:='Test123';
s2:=SplitAtPos(s1,8);
dbms_output.put_line('s1='||s1||' s2='||s2);

s1:='Test123';
s2:=SplitAtPos(s1,0);
dbms_output.put_line('s1='||s1||' s2='||s2);

end;

Comments

[alex]

An example is very appreciated.

[OMG Ponies]

REGEXP_REPLACE is an option for Oracle 10g+: download.oracle.com/docs/cd/B19306_01/server.102/b14200/…

[alex]

Works. Thanks! I was thinking about regex but they are kinda complicated.

[OMG Ponies]

@alex: I highly recommend regular-expressions.info -it's a great resource to get more comfortable with regexes.