SQL: Delimited list of values as in-line view. csv on memory

I have a csv in my copy&paste buffer of 100 values where each values is 10 byte length strings.
And I want to use these values in SQL as a key to look up a table.
I think this often happens for a lot of people especially Excel workers.
With Excel, one can use Oracle provided driver or more expensive Oracle products.

But I want to use sqlplus so I googled.
I have to admit that I started searching with wrong keyword like 'UNPIVOT'.
So, it took a while to find a relevant page like below.

Converting delimited lists to collections

It is a common requirement to be able to take a delimited list of values, say A,B,C,D, and treat this data like it was a set of rows in a table

While I learned a lot from this page, I thought there may be a better way.
So, I googled once again and found the answer in the 'usual' place... "Ask Tom".

Ask Tom "Clever SQL"

scott@10G> exec :txt := 'a,bb,ccc,d,e,f';
scott@10G> select trim(regexp_substr(upper(:txt),'[^,]+',1,level)) as Tokens from dual
connect by level <= length(regexp_replace(:txt,'[^,]'))+1;

TOKENS
--------------------------------
A
BB
CCC
D
E
F

6 rows selected.

Great! I think my sample below is a too simple but easier to understand.

Please notice 2 differences.

  1. I didn't use bind variable so it's a true one-liner SQL.
  2. Number of values need not to be known. I just used a sufficiently large value which is '256'.
SQL> l
  1  with tokens as (
  2  select regexp_substr('KING,TURNER,BLAH,!ERROR!,0,JAMES','[^,]+',1,level) as token
  3  from dual connect by level <= 256)
  4* select ename,deptno from emp,tokens where emp.ename=tokens.token
SQL> /

ENAME                              DEPTNO
------------------------------ ----------
KING                                   10
TURNER                                 30
JAMES                                  30





Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today