CODEWITHSUNDEEP
oracle-databasestringreplacesubstring
alex
alex

Reputation: 23

Remove a character from a given position on Oracle

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?

Upvotes: 2

Views: 31264

Answers (5)

JPIN
JPIN

Reputation: 1

You can use something like this in pl/SQL

DECLARE 
    v_open    NUMBER;
    v_string1 VARCHAR2(10);
    v_string2 VARCHAR2(10);
    v_word    VARCHAR2(10);
BEGIN
    v_open := INSTR('PANCAKES' ,'A',1);
    v_string1 := SUBSTR('PANCAKES' ,1, 1);
    v_string2 := SUBSTR('PANCAKES' ,v_open+1);
    v_word    := v_string1||v_string2;
END;

Upvotes: 0

Jan
Jan

Reputation: 2273

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;

Upvotes: 1

Blza Box
Blza Box

Reputation: 345

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.

Upvotes: 4

OMG Ponies
OMG Ponies

Reputation: 332541

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:

Upvotes: 4

Randy
Randy

Reputation: 16677

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.

Upvotes: 0

Related Questions