You must Sign In to post a response.
  • Category: SQL Server

    split function in oracle

    Hi,

    In oracle i want to split string. don't know hot to do that.

    here is the string str ='1,2,3,3'

    I want such type 1
    2
    3

    Pl help me
    thanks,
  • #215420
    hi,
    for that you refer this link
    http://www.oracle.com/technology/oramag/code/tips2004/030104.html
    http://www.oracle.com/technology/oramag/code/tips2007/070907.html

    Thanks & Regards
    Patel Vipul

  • #215546
    Hi,
    First create a type

    CREATE OR REPLACE
    type split_tbl as table of varchar2(32767);
    /


    and

    CREATE OR REPLACE function split
    (
    p_list varchar2,
    p_del varchar2 := ','
    ) return split_tbl pipelined
    is
    l_idx pls_integer;
    l_list varchar2(32767) := p_list;
    l_value varchar2(32767);
    begin
    loop
    l_idx := instr(l_list,p_del);
    if l_idx > 0 then
    pipe row(substr(l_list,1,l_idx-1));
    l_list := substr(l_list,l_idx+length(p_del));

    else
    pipe row(l_list);
    exit;
    end if;
    end loop;
    return;
    end split;
    /



    Thanks -- Vj

    Thanks -- Vijaya Kadiyala
    http://www.DotNetVJ.com
    Microsoft MVP


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.