Solved

selecting from a comma delimited list

Posted on 2003-10-21
6
1,112 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 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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
Oracle Query - Convert letters to numbers and display the difference 3 62
Checking for column width 8 49
SQL query for highest sequence 4 75
Oracle programming for starter 14 76
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

751 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