separate numeric and non-numeric values from alphanumeric

[venkataramana22]

have column named "mixed"
data in that column is like..

12acdf45
534fdfddy5
gfg56789
fgas636382

So, i wld like to retrieve only numericals from those OR
only characters..
the result should be either -- 1245,5345... or
acdf,fdfddy,gfg
i need a query to this retreval of data.

[Saii]

try in-built function TRANSLATE

[venkataramana22]

[quote=Saii]try in-built function TRANSLATE[/QUOT

my table name is alphnumer
can u give me full query plz(total 'select' statement).

[amitpatel66]

[quote=venkataramana22]

try in-built function TRANSLATE[/QUOT

my table name is alphnumer
can u give me full query plz(total 'select' statement).

 
DECLARE
CURSOR C1 IS SELECT * FROM alphnumer;
v_num VARCHAR2(100) := NULL;
v_char VARCHAR2(100) := NULL;
v_length NUMBER := 0;
v_asc_val NUMBER := 0;
BEGIN
FOR I IN C1 LOOP
IF(C1%FOUND) THEN
v_length := LENGTH(I.mixed);
FOR J IN 1..v_length LOOP
v_asc_val:= ASCII(SUBSTR(I.mixed,J,1));
--DBMS_OUTPUT.PUT_LINE(SUBSTR(I.mixed,J,1));
IF (v_asc_val >= 97 AND v_asc_val <= 122) THEN
v_char:= TO_CHAR(v_char||SUBSTR(I.mixed,J,1));
ELSE
v_num:= TO_CHAR(v_num||SUBSTR(I.mixed,J,1));
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_num||','||v_char);
v_num:=NULL;
v_char:= NULL;
END IF;
END LOOP;
END;
 
 

The above block is to seperate the numbers and alphabets from a single column...hope this helps!!

[bonzi1405]

have column named "mixed"
data in that column is like..

12acdf45
534fdfddy5
gfg56789
fgas636382

So, i wld like to retrieve only numericals from those OR
only characters..
the result should be either -- 1245,5345... or
acdf,fdfddy,gfg
i need a query to this retreval of data.

select replace(translate('12acdf45534fdfddy5gfg56789fgas6 36382','abcdefghijklmnopqrstuvwxyz','~'),'~','') from dual

select replace(translate('12acdf45534fdfddy5gfg56789fgas6 36382','0123456789','~'),'~','') from dual