sql - How to remove repeated commas and trim from each end using REGEXP_REPLACE? -

i need concatenate several fields together, may or may not null. potentially end string like: ',,c,,e,,' want show 'c,e'.

i can via combination of regexp_replace , trim:

with sd (select 'a,b,c' str dual union             select 'a' str dual union             select null str dual union             select 'a,,,d' dual union             select 'a,,,,e,f,,'from dual union             select ',,,d,,f,g,,'from dual) select str,        regexp_replace(str, '(,)+', '\1') new_str,        trim(both ',' regexp_replace(str, '(,)+', '\1')) trimmed_new_str   sd;  str         new_str     trimmed_new_str ----------- ----------- --------------- a,b,c       a,b,c       a,b,c                                              a,,,d       a,d         a,d             a,,,,e,f,,  a,e,f,      a,e,f           ,,,d,,f,g,, ,d,f,g,     d,f,g   

but feel ought doable in single regexp_replace can't work out life of me how done!

is possible? if so, how?

query:

with sd (select 'a,b,c' str dual union             select 'a' dual  union             select null dual union             select 'a,,,d,' dual  union             select ',a,,,d' dual  union             select ',a,,,d,' dual  union             select ',,,a,,,d,,,' dual  union             select ',a,,,,,e,f,,' dual union             select ',,d,,f,g,,' dual ) select str,        regexp_replace(str, '^,+|,+$|,+(,\w)','\1') new_str   sd; 

result:

str             new_str ----------------------- a,b,c           a,b,c               (null)          (null)   a,,,d,          a,d ,a,,,d          a,d ,a,,,d,         a,d ,,,a,,,d,,,     a,d ,a,,,,,e,f,,    a,e,f ,,d,,f,g,,      d,f,g 

pattern:

  ^,+       matches commas @ beginning   |         or   ,+$       matches commas @ end   |         or   ,+(,\w)   matches several commas followed single comma , word. 

replaces above first sub expression, comma , word.