OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

How to SELECT from a comma delimited list

January 25th, 2006 By Eddie Awad

HR@XE> create type mytype as table of varchar2(10);
  2  /

Type created.

HR@XE> select * from table (mytype(1,2,'a'))
  2 /

COLUMN_VALUE
---------------------
1
2
a

Or, instead of creating your own type, you can use a pre-defined one:

HR@XE> select * from table(sys.dbms_debug_vc2coll(1,2,'a'))
  2  /

COLUMN_VALUE
---------------------
1
2
a

3 Responses to “How to SELECT from a comma delimited list”

  1. tkyte Says:

    but that is not a comma delimited list. That is a collection. Entirely different beast alltogether.

    most people would be thinking of a comma delimited list like this:

    ‘1,2,3,4’

    that won’t select from ‘1,2,3,4’

    http://tkyte.blogspot.com/2006/01/how-can-i.html

  2. Eddie Awad Says:

    By a comma delimited list, I meant exactly what most people think: a list of values seperated by a comma, like this ‘1,2,3,4’ or like this ‘a,b,c,d,’ or like this ‘1,a,3,d’….

    Yes, you won’t select from ‘1,2,3,4′. But, what I was trying to show here was that, given a comma delimited list, you could transform each list element to a “row” using a collection type and a table function.

  3. Eddie Awad Says:

    One more thing, if you want to bind (or parametrize) the (1,2,’a’) in select * from table (mytype(1,2,’a’)), in addition to Tom’s example, Francois has another nice one too.

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question