Tuesday Apr 16, 2013

tokenizing newline delimited data with regexp_substr() in sqlplus

In my previous post, I showed comma delimited values(csv) example.

SQL: Delimited list of values as in-line view. csv on memory (INOUE Katsumi @ Tokyo)

select regexp_substr('KING,TURNER,BLAH,!ERROR!,0,JAMES','[^,]+',1,level) as token from dual connect by level <= 256

But if you copy and paste from Excel like below,

It will be 'newline' delimited data.  Below is sqlplus session showing regexp_substr() can handle it.
I tried regex '\r' and '\n' but neither worked.

SQL> l
  1  select regexp_substr('
  2  King
  3  Kochhar
  4  De Haan
  5  Hunold
  6  Ernst
  7  Austin
  8  Pataballa
  9  Lorentz
 10  Greenberg
 11  Faviet
 12  Chen
 13  ','[^
 14* ]+',1,level) from dual connect by level <= 12
SQL> /

REGEXP_SUBSTR('KINGKOCHHARDEHAAN
--------------------------------
King
Kochhar
De Haan
Hunold
Ernst
Austin
Pataballa
Lorentz
Greenberg
Faviet
Chen

12 rows selected.
About

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

Search

Archives
« April 2013 »
SunMonTueWedThuFriSat
 
1
2
3
4
5
6
7
9
10
11
12
13
14
20
21
23
24
27
28
29
    
       
Today