Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

selecting from a comma delimited list

Posted on 2003-10-21
6
Medium Priority
?
1,116 Views
Last Modified: 2012-05-04
Hi,
Is there a way of selecting from a delimited list in SQL.

for instance if i have a string eg '1,2,3,4,5' is there a way from employing SQL to give me 5 rows
1
2
3
4
5
?
the reason is that i have a variable with this data in and want to use dynamic sql to loop through this data.
0
Comment
Question by:mwoolger
[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
6 Comments
 
LVL 3

Accepted Solution

by:
AnnetteHarper earned 1000 total points
ID: 9590525
I don't believe that parsing the comma delimited string would be possible from a simple SQL statement unless the number of values is fixed and known.

Here's a solution that works for any number of values. It uses a temp table to hold the parsed values. Create the temp table, then just call the procedure to parse the string. You can then loop through the temp table to execute your statement for each value.

CREATE GLOBAL TEMPORARY TABLE "X2"."NUMBER_PARAM_TEMP"
   (      "NUMBER_PARAM" NUMBER(10,0) NOT NULL ENABLE,
       CONSTRAINT "NUMBER_PARAM_TEMP_PK" PRIMARY KEY ("NUMBER_PARAM") ENABLE
   )  ON COMMIT PRESERVE ROWS;

  PROCEDURE PARAM_TO_NUMBER_PARAM_TEMP(Param IN VARCHAR2)
  IS
    Param_Str VARCHAR2(32767);
    Comma_Position NUMBER;
  BEGIN
    DELETE FROM Number_Param_Temp;
    Param_Str:=Param||',';
    LOOP
      Comma_Position := INSTR(Param_Str, ',');
      EXIT WHEN (NVL(Comma_Position, 0)) = 0;
      INSERT INTO Number_Param_Temp VALUES (TRIM(SUBSTR(Param_Str, 1, Comma_Position-1)));
      Param_Str := SUBSTR(Param_Str, Comma_Position+1);
    END LOOP;
  END PARAM_TO_NUMBER_PARAM_TEMP;
0
 

Author Comment

by:mwoolger
ID: 9590619
Excellent. didnt need the temporary table - just the algorithm.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9590733
even simpler, in PL/SQL, there is a utility package: DBMS_UTILITY.comma_to_table()
can convert comma-delimited string into a PL/SQL table in one function call.

for example:

DBMS_UTILITY.comma_to_table('<string>', table_length, <pl/sql_tabname>);

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 5

Expert Comment

by:DrJekyll
ID: 9591448
Here is an example of seazodiac suggestion

CREATE OR REPLACE PROCEDURE COMMA_SAMPLE AS  
mytable DBMS_UTILITY.uncl_array;  
mylist VARCHAR2(80);  
mytable_count NUMBER;
BEGIN  
   mylist := '1,2,3,4,5';
   DBMS_UTILITY.COMMA_TO_TABLE(mylist, mytable_count, mytable);
   mylist := 'Empty.';
   DBMS_OUTPUT.PUT_LINE('MYTABLE: ');  
   DBMS_OUTPUT.PUT_LINE('---------------------------');  
   FOR item IN 1..mytable_count LOOP    
      DBMS_OUTPUT.PUT_LINE(mytable(item));  
   END LOOP;   DBMS_OUTPUT.PUT_LINE('');  
END;
 /
0
 
LVL 5

Expert Comment

by:Pontis
ID: 9592124

 One bug I've had with dbms_utility.comma_to_table is that when you have data that starts with number, it fails, works fine if data has alpha character as first symbol (the same as with identifiers in Oracle), So, for example

mylist := '1,2,3,4,5';
   DBMS_UTILITY.COMMA_TO_TABLE(mylist, mytable_count, mytable);

will fail where

mylist := 'a1,a2,a3,a4,a5';
   DBMS_UTILITY.COMMA_TO_TABLE(mylist, mytable_count, mytable);

Worked OK. The workaround was to add alpha character in app that passed the list (VB) and unparse it in Oracle PL/SQL. It was with 9i (9.2.01 I believe )
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9593296
Pontis is correct. Number character does cause the problem.

but this can be solved easily by doing "prefixing it with a letter and replacing it later"

for example: if you have a string of "1,2,3,4,5"
you can prefix the digits with a fixed letter to make it look like this: 'a1,a2,a3,a4,a5'

then do :
DBMS_UTILITY.COMMA_TO_TABLE(mylist, mytable_count, mytable);

then when you use it , you do "replace(mytable(i), 'a', '')" function to get rid of 'a'.

that's all you need to do ..



0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

721 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