How can I select from list of values in Oracle

Question

I am referring to this stackoverflow answer  How can I select from list of values in SQL Server How could something similar be done in Oracle  I ve seen the other answers on this page that use UNION and although this method technically works  it s not what I would like to use in my case  So I would like to stay with syntax that more or less looks like a comma-separated list of values  UPDATE regarding the create type table answer  I have a table  CREATE TABLE BOOK      quot BOOK ID quot  NUMBER 38 0     I use this script but it does not insert any rows to the BOOK table  create type number tab is table of number   INSERT INTO BOOK       BOOK ID   SELECT A NOTEBOOK ID FROM      select column value AS NOTEBOOK ID from table  number tab 1 2 3 4 5 6    A    Script output  TYPE number tab compiled Warning  execution completed with warning  But if I use this script it does insert new rows to the BOOK table  INSERT INTO BOOK       BOOK ID   SELECT A NOTEBOOK ID FROM      SELECT  LEVEL-1  1 AS NOTEBOOK ID FROM DUAL CONNECT BY LEVEL lt  6  A

User · Answer

There are various ways to take a comma-separated list and parse it into multiple rows of data. In SQL

SQL> ed
Wrote file afiedt.buf

  1  with x as (
  2    select '1,2,3,a,b,c,d' str from dual
  3  )
  4   select regexp_substr(str,'[^,]+',1,level) element
  5     from x
  6* connect by level <= length(regexp_replace(str,'[^,]+')) + 1
SQL> /

ELEMENT
----------------------------------------------------
1
2
3
a
b
c
d

7 rows selected.

Or in PL/SQL

SQL> create type str_tbl is table of varchar2(100);
  2  /

Type created.

SQL> create or replace function parse_list( p_list in varchar2 )
  2    return str_tbl
  3    pipelined
  4  is
  5  begin
  6    for x in (select regexp_substr( p_list, '[^,]', 1, level ) element
  7                from dual
  8             connect by level <= length( regexp_replace( p_list, '[^,]+')) + 1)
  9    loop
 10      pipe row( x.element );
 11    end loop
 12    return;
 13  end;
 14
 15  /

Function created.

SQL> select *
  2    from table( parse_list( 'a,b,c,1,2,3,d,e,foo' ));

COLUMN_VALUE
--------------------------------------------------------------------------------
a
b
c
1
2
3
d
e
f

9 rows selected.

User · Answer

Starting from Oracle 12 2  you don t need the TABLE function  you can directly select from the built-in collection   SQL gt  select   FROM sys odcinumberlist 5 2 6 3 78    COLUMN VALUE ------------            5            2            6            3           78  SQL gt  select   FROM sys odcivarchar2list  A   B   C   D     COLUMN VALUE ------------ A B C D

User · Answer

If you are seeking to convert a comma delimited list of values   select column value  from table sys dbms debug vc2coll  One    Two    Three    Four      -- Or  select column value  from table sys dbms debug vc2coll 1 2 3 4      If you wish to convert a string of comma delimited values then I would recommend Justin Cave s regular expression SQL solution

User · Answer

Hi it is also possible for Strings with XML-Table  SELECT trim COLUMN VALUE  str FROM xmltable       REPLACE  a1  b2  a2  c1

User · Answer

You don t need to create any stored types  you can evaluate Oracle s built-in collection types    select distinct column value from table sys odcinumberlist 1 1 2 3 3 4 4 5

User · Answer

You can do this   create type number tab is table of number   select   from table  number tab 1 2 3 4 5 6      The column is given the name COLUMN VALUE by Oracle  so this works too   select column value from table  number tab 1 2 3 4 5 6

[sql] How can I select from list of values in Oracle

Examples related to sql

Examples related to oracle

Examples related to select

Examples related to oracle11g