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?
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:
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.
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;
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.
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;
