pshik
asked on
Split string function in Oracle
Hi,
can someone help me write a function to split a string separated by '/' into separate values, please? (Oracle 10g)
say, I have 'one/two/three/four' and need
1
2
3
4
Thanks!
can someone help me write a function to split a string separated by '/' into separate values, please? (Oracle 10g)
say, I have 'one/two/three/four' and need
1
2
3
4
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
to sdstuber:
yes, I saw that example when I googled for it. Unfortunately, it doesn't work for me. Thanks though:)
Regarding converting, I don't need it, it was a typo, sorry.
yes, I saw that example when I googled for it. Unfortunately, it doesn't work for me. Thanks though:)
Regarding converting, I don't need it, it was a typo, sorry.
doesn't work? what does it not do?
if you won't want the data returned in rows, what are you trying to do?
if you won't want the data returned in rows, what are you trying to do?
ASKER
sdstuber,
it's a school project and we haven't covered pipeline so i can't use it. Besides, it gives me error messages. Thanks anyway:)
I am going to go with the query. Thanks sujith80!
it's a school project and we haven't covered pipeline so i can't use it. Besides, it gives me error messages. Thanks anyway:)
I am going to go with the query. Thanks sujith80!
it's a slight variation from Tom Kyte's function found on askTom
CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
RETURN vcarray PIPELINED
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start);
IF v_index = 0
THEN
PIPE ROW(SUBSTR(p_string, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP;
RETURN;
END str2tbl;
select * from table(str2tbl('one/two/thr
as for converting from one to 1, two to 2, you'll have to write your own number translator