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

by


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;

2

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