Oracle 11gR2: split string with multiple delimiters(add)

Question

String val = "ABC,abc|DEF,def|GHI,g hi|JKL,jkl";

How to split this string and insert into tables using sql, pl/sql or whatever.

, is column delimiter and | is row delimiter.

is it possible?

[expected result]

col1    col2
------------
ABC     abc
DEF     def
GHI     g hi
JKL     jkl

Addition Question

Thanks reply. I have a another question.

this is a string.

String val = "ABC,abc||D|@EF,def||G|HI,g hi||JKL,jkl";

I want to split by only || delimiter. how to use the RegExp?

I try it such as '[^|]{2}+', '^[|]{2}+', 1, etc..

this is my wrong result.

[wrong result]

COL1       COL2
---------- ----------
BC         abc
           D
@EF        def
           G
HI         g hi
JKL        jkl

[expected result]

col1    col2
-----------
ABC     abc
D|@EF   def
G|HI    g hi
JKL     jkl

Avrajit Roy · Answer 1 · 2015-12-14T10:46:43.030

1

Try this. It will help you out.

SELECT SUBSTR(A.tx,1,instr(a.tx,',',1)-1) col1,
  SUBSTR(A.tx,instr(a.tx,',',1)       +1,LENGTH(a.tx)) col2
FROM
  (SELECT TRIM(regexp_substr(REPLACE('ABC,abc||D|@EF,def||G|HI,g hi||JKL,jkl','||','$'),'[^$]+', 1, level)) tx
  FROM dual
    CONNECT BY regexp_substr(REPLACE('ABC,abc||D|@EF,def||G|HI,g hi||JKL,jkl','||','$'), '[^$]+', 1, level) IS NOT NULL
  )A;

edited Dec 14 '15 at 10:46

answered Dec 14 '15 at 09:45

Avrajit Roy

3,233
1
13
25

this query is works! thanks. and I modify question. Please check it. – Jaehyuk Choi Dec 14 '15 at 10:30
Check it now. It will suffice your requirement. – Avrajit Roy Dec 14 '15 at 10:47
Wow!! Thank you!! This is just what i wanted!! – Jaehyuk Choi Dec 14 '15 at 10:51
I'm afraid this fails if you have a NULL element or if your data contains a dollar sign. Can you guarantee your data will not contain NULL's or dollar signs? Always expect the unexpected. – Gary_W Dec 14 '15 at 21:08

score 0 · Answer 2 · answered Dec 14 '15 at 09:41

0

Try this. Hope this helps.

SELECT  
REGEXP_SUBSTR (str, '[^,]+', 1, 1)    AS COL1
,REGEXP_SUBSTR (str, '[^,]+', 1, 2)    AS COL2
from (

  select  
   trim(regexp_substr('ABC,abc|DEF,def|GHI,ghi|JKL,jkl', '[^|]+', 1, LEVEL)) str
  FROM 
  DUAL
  CONNECT BY instr('ABC,abc|DEF,def|GHI,ghi|JKL,jkl', '|', 1, LEVEL - 1) > 0
  )

answered Dec 14 '15 at 09:41

Ruelos Joel

2,209
3
19
33

The answer is a combination of two threads.(splitting into rows and splitting into columns) http://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle https://community.oracle.com/thread/2348338?start=0&tstart=0 – Mathias Magnusson Dec 14 '15 at 09:50
I believe you can simplify the `connect by` line by using `REGEXP_COUNT`: `CONNECT BY level <= regexp_count('ABC,abc|DEF,def|GHI,ghi|JKL,jkl', '\|')+1` It counts the delimiters then adds 1 to get a count of the fields. Logically simpler anyway? – Gary_W Dec 14 '15 at 21:17

score 0 · Answer 3 · answered Dec 14 '15 at 18:59

We can use CTE(Common Table Expressions).  For the first part of your query try this:

with cte(i,strr1,strr2) as
(
select 1 as i,regexp_substr('ABC,abc|DEF,def|GHI,g hi|JKL,jkl','[A-Z]+',1,1) as strr1,regexp_substr('ABC,abc|DEF,def|GHI,g hi|JKL,jkl','[a-z]+(\s)*[a-z]+|[a-z]+(\s)*[a-z]+|',1,1) as strr2 from dual
union all
select i+1,regexp_substr('ABC,abc|DEF,def|GHI,g hi|JKL,jkl','[A-Z]+',1,i+1) as strr1,regexp_substr('ABC,abc|DEF,def|GHI,g hi|JKL,jkl','[a-z]+(\s)*[a-z]+|[a-z]+(\s)*[a-z]+|',1,i+1) as strr2 from cte where i+1<5
)select strr1,strr2 from cte;

score 0 · Accepted Answer · answered Dec 14 '15 at 21:55

Here's a different approach that handles nulls and does not risk altering any data. It uses a with clause to break down the data by the delimiters, ending up with splitting on the commas.

ASSUMPTION: Steps have already been taken to ensure that the data has already been scrubbed of the delimiter characters (commas and pipes).

-- Original data with multiple delimiters and a NULL element for testing.
with orig_data(str) as (
  select 'ABC,abc||||G|HI,g hi||JKL,jkl' from dual 
),
--Split on first delimiter (double-pipes)
Parsed_data(rec) as (
  select regexp_substr(str, '(.*?)(\|\||$)', 1, LEVEL, NULL, 1)
  from orig_data
  CONNECT BY LEVEL <= REGEXP_COUNT(str, '\|\|') + 1 
)
-- For testing-shows records based on 1st level delimiter
--select rec from parsed_data;

-- Split the record into columns
select regexp_replace(rec, '^(.*),.*', '\1') col1,
       regexp_replace(rec, '^.*,(.*)', '\1') col2
from Parsed_data;

I'm totally satisfied it. Thank you!! It very works. I could learn a lot from this query! — Jaehyuk Choi, Dec 18 '15 at 14:43
Cool, that's what we're all here for. :-) Whatever method you choose, be sure to document it for the person maintaining the code after you. — Gary_W, Dec 18 '15 at 15:11

Oracle 11gR2: split string with multiple delimiters(add)

Addition Question

4 Answers4

Linked