-- Using REGEXP_REPLACE(), a string with multiple gaps can be formated to a 
-- string with single gap/whitespace. The {2,6} allows gaps between strings 
-- of length between 2 and 6 will be formated to string with single spacing
-- by using
-- REGEXP_REPLACE(tst_str,'[[:blank:]]{2,6}',' ')

WITH q_str AS
(SELECT   'String  With   Big  Gaps' tst_str FROM DUAL)
SELECT
 CAST(tst_str AS VARCHAR2(30)) tst_str,
 REGEXP_REPLACE(tst_str,'[[:blank:]]{2,6}',' ') single_gap_str 
FROM q_str;



-- The formated string can be split into single words using
-- REGEXP_SUBSTR(single_gap_str,'[^\ ]+',1,LEVEL)
-- and
-- REGEXP_COUNT(single_gap_str,' ')+1

WITH q_str AS
(SELECT   'String  With   Big  Gaps' tst_str FROM DUAL),
q_formated AS
(SELECT
  CAST(tst_str AS VARCHAR2(30)) tst_str,
  REGEXP_REPLACE(tst_str,'[[:blank:]]{2,6}',' ') single_gap_str
 FROM q_str)
SELECT
 single_gap_str,
 LEVEL  word_idx,
 CAST(REGEXP_SUBSTR(single_gap_str,'[^\ ]+',1,LEVEL) AS
       VARCHAR2(15)) single_word
FROM q_formated
CONNECT BY LEVEL <= REGEXP_COUNT(single_gap_str,' ')+1;



-- A CSV string can be split into respective data elements and loaded into 
-- a table (example below: customer_code, sale_date, sales_amount, tax)
-- using 
-- REGEXP_SUBSTR(csv_str,'[^\,]+',1,LEVEL)
-- and
-- REGEXP_COUNT(csv_str,',')+1

SELECT
 csv_str,
 LEVEL value_idx,
 CAST(REGEXP_SUBSTR(csv_str,'[^\,]+',1,LEVEL) AS
       VARCHAR2(15)) single_value
FROM  (SELECT 'A10001,05/31/2014,5234.23,28.34' csv_str FROM DUAL)
CONNECT BY LEVEL <= REGEXP_COUNT(csv_str,',')+1;