?
Solved

Split string function in Oracle

Posted on 2007-11-21
5
Medium Priority
?
20,710 Views
Last Modified: 2013-12-18
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
Comment
Question by:pshik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
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

by:
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

by:pshik
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

by:sdstuber
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

by:pshik
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question