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
( "BOOK_ID" 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<=6) A
;
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.
Starting from Oracle 12.2, you don't need the TABLE
function, you can directly select from the built-in collection.
SQL> select * FROM sys.odcinumberlist(5,2,6,3,78);
COLUMN_VALUE
------------
5
2
6
3
78
SQL> select * FROM sys.odcivarchar2list('A','B','C','D');
COLUMN_VALUE
------------
A
B
C
D
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.
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));
Hi it is also possible for Strings with XML-Table
SELECT trim(COLUMN_VALUE) str FROM xmltable(('"'||REPLACE('a1, b2, a2, c1', ',', '","')||'"'));
Source: Stackoverflow.com