SQL>create table t (val varchar2(1000));
Table created.
SQL>
SQL>insert into t values (',Accenture, , ,EDS,Sun,Training ');
1 row created.
SQL>insert into t values ('United States,,,, ');
1 row created.
SQL>insert into t values ('Canada,');
1 row created.
SQL>insert into t values (',Canada ');
1 row created.
SQL>select val,
2 trim( both ',' from regexp_replace( replace( trim(val), ' ,', ',' ), '(,){2,}', '\1') ) a,
3 trim(',' from regexp_replace(val, '( *, *){2,}', ',')) b
4 from t;
VAL A B
--------------------------------- --------------------------- ---------------------------
,Accenture, , ,EDS,Sun,Training Accenture,EDS,Sun,Training Accenture,EDS,Sun,Training
United States,,,, United States United States
Canada, Canada Canada
,Canada Canada Canada