-- 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;
17275