Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Split string function in Oracle

Posted on 2007-11-21
Medium Priority
20,759 Views
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!
0
Question by:pshik
• 2
• 2

LVL 74

Expert Comment

ID: 20329588
Here's a function to split the string on any delimiter
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/three/four','/'))

as for converting from one to 1, two to 2,  you'll have to write your own number translator
0

LVL 27

Accepted Solution

sujith80 earned 500 total points
ID: 20332366
Use the following query:

WITH Y as (select  'one/two/three/four' val from dual)
select substr(val, (decode(level, 1, 0 , instr(val,'/',1, level-1) ) + 1) ,
(decode( instr(val,'/',1, level) -1 , -1, length(val), instr(val,'/',1, level) -1 )) - (decode(level, 1, 0 , instr(val,'/',1, level-1) ) + 1) + 1) a
from Y connect by level <= (select (length(val) - length(replace(val , '/', null)) ) from Y ) + 1
/
0

Author Comment

ID: 20335564
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.
0

LVL 74

Expert Comment

ID: 20335648
doesn't work?  what does it not do?

if you won't want the data returned in rows, what are you trying to do?
0

Author Comment

ID: 20343068
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!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
###### Suggested Courses
Course of the Month20 days, 21 hours left to enroll