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

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

There are various ways to take a comma-separated list and parse it into multiple rows of data   In SQL  SQL gt  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  lt   length regexp replace str             1 SQL gt     ELEMENT ---------------------------------------------------- 1 2 3 a b c d  7 rows selected    Or in PL SQL   SQL gt  create type str tbl is table of varchar2 100     2     Type created   SQL gt  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  lt   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 gt  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

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

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

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

User · Answer

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

[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