Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

selecting from a comma delimited list

Posted on 2003-10-21
6
Medium Priority
?
1,117 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
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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…
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.

885 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