convert comma delimted list into a table

[cmmrfrds]

Is there an easy way with SQL code to convert a large comma delimited list into a temp table. I would put the code in a With Clause and use it in a subsequent join.

The reason is, I am modifying some SQL that has several large in lists and I want to make these tables instead (temporary for duration of the query).

I appreciate any ideas. Thank you.

 

[Turkbear]

Hi,
Oracle's ability to use an external table may give you what you need:

http://www.orafaq.com/node/848

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[taupirho]

Depending on how large and unwieldy your list is, perhaps you can build on something like this:-

1 with data as
2 (
3 select trim
4 (
5 substr(list,
6 instr(list, ',' , 1, level) + 1,
7 instr(list , ',' , 1, level+1) -
8 instr(list , ',' , 1, level) -1)
9 ) as list_member
10 from
11 (
12 select ','||'1,2,3,4,5,6,7,8,9'||',' list
13 from dual
14 )
15 connect by level <= length('1,2,3,4,5,6,7,8,9')-
16 length(replace('1,2,3,4,5,6,7,8,9',',',''))+1
17 )
18* select * from data
19 /

LIST_MEMBER
-------------------
1
2
3
4
5
6
7
8
9

9 rows selected.

SQL>


In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

cmmrfrds,

Following is a generic procedure that accepts any character string, with any delimiter, and populates a temporary table with a row for each delimited value from the incoming string. Multiple sessions can access this procedure (and the global temporary table) without contention. For simplicity of demonstration, I have done everything in SQL*Plus:

create global temporary table GTT_Hold_Delimited_As_Rows
      (String varchar2(2000));

Table created.

create or replace procedure Delimited_To_Rows
      (String_in varchar2,delim varchar2)
is
    remainder                      varchar2(4000);
begin
    remainder := string_in;
    while length(remainder)>0 loop
        insert into GTT_Hold_Delimited_As_Rows
               values (substr(remainder,1,instr(remainder||delim,delim)-1));
        remainder := substr(remainder,instr(remainder||delim,delim)+length(delim));
    end loop;
end;
/

Procedure created.

Here are examples of the invocation, and use, of the procedure Delimited_To_Rows:

exec Delimited_To_Rows('a,b,c,d,e',',')

PL/SQL procedure successfully completed.

select * from GTT_Hold_Delimited_As_Rows;

STRING
-----------------
a
b
c
d
e

5 rows selected.

commit;

Commit complete.

exec Delimited_to_rows('1 whatever 2 whatever 3 whatever 4',' whatever ')

PL/SQL procedure successfully completed.

select * from GTT_Hold_Delimited_As_Rows;

STRING
----------------
1
2
3
4

4 rows selected.

Let us know if you have any questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[dbomrrsm]

SELECT     REGEXP_SUBSTR ('a,b,c,d,e,f,g,h', '[^,]+', 1, LEVEL) pivot_char
      FROM DUAL
CONNECT BY REGEXP_SUBSTR ('a,b,c,d,e,f,g,h', '[^,]+', 1, LEVEL) IS NOT NULL

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]