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

Personal View of a Sales Engineer in Tokyo.

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