Solved

selecting from a comma delimited list

Posted on 2003-10-21
6
1,111 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 250 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Consolidating oracle query results to a single line 8 65
pl/sql - query very slow 26 74
Field name with special character (Ñ) in Oracle 11 96
Oracle Insert not working 10 32
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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

820 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