Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2010
    Posts
    8

    Unanswered: Using comma separator in oracle

    Hi frds,
    I want the solution for this

    CREATE TABLE TEMP (id varchar(100));

    i insert data like this

    insert into temp('1000-2,1000-3,1000-4');

    now i want to select one by one seperated by comma

    thank u

    with regards
    chaitanya.p

  2. #2
    Join Date
    Nov 2010
    Posts
    6
    Hi-

    May be this is helpful.

    I found this on internet.

    Just create a function and call the function in sql query.

    create or replace type split_tbl as table of varchar2(32767);
    /
    show errors;

    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;
    AA
    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;
    /
    show errors;


    With this function, I can run a query like this:

    SQL> select * from table(split('one,two,three'));

    one
    two
    three

  3. #3
    Join Date
    Nov 2010
    Posts
    8

    thank q

    thank q
    it's working .......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •