sql concatenate field separated by comma - eliminating null values and commas

This can really mess with your results and produce some inaccuracies.

The quick and dirty:  replace more than one comma with one.

regexp_replace(mystring,',{2,}',',')

Now for the issue:
field1 = a
field2 = b
field3 = null
field 4 = c

produces: a,b,c

field1 = a
field2 = null
field3 = b
field 4 = c

also produces: a,b,c

I was hoping there was a better solution than replacing

I don't think the replace will work if the first or last column is null.  Then you would have a leading or trailing ,.

I assume you query looks something like this:

select field1 || ',' || field2 || ',' ||...


Change that to

select field1 || nvl2(field1, ',', '') || field2 || nvl2(field2, ',', '') ...

That should handle the leading and trailing commas as well.

mmm, not sure nvl2 solves this, e.g. these produce trailing commas

select
   'a' || nvl2('a', ',', '') 
|| 'b' || nvl2('b', ',', '')
|| 'c' || nvl2('c', ',', '')
|| null
from dual
;

select
   'a' || nvl2('a', ',', '') 
|| 'b' || nvl2('b', ',', '')
|| null || nvl2(null, ',', '')
|| null
from dual
;

Select allOpen in new window

not sure that replace isn't the simplest solution, but here's one without it:

,select
     case when field1 is not null then field1 end
  || case when field1 is not null and  coalesce(field2,field3,field4) is not null then ',' end
  || case when field2 is not null then field2 end
  || case when field2 is not null and  coalesce(field3,field4) is not null then ',' end
  || case when field3 is not null then field3 end
  || case when field3 is not null and  field4 is not null then ',' end
  || case when field4 is not null then field4 end
  as by_case
from table1

Select allOpen in new window

Actually even replace will not work for trailing comma's.

A quick RTRIM will fix that but I guess is replace is out, so is RTRIM...

Not sure why replace isn't a good solution.  regexp functions are expensive but I'm not sure it they are more expensive than several NVL2 calls when everything is considered...

Of course, testing will show the best way.

>>not sure that replace isn't the simplest solution, but here's one without it:

replace has to be better than all the case and coalesce calls...

:) agreed, but I enjoyed the exercise (it does produce the desired result as far as I can tell)

Depending on how the data is being fetched, you might give LISTAGG a try (when running on 11.2):

select listagg(a.str, ',') within group(order by a.row_num) expression
  from (select 1 row_num,
               'a' str
          from dual
        union
        select 2 row_num,
               'b' str
          from dual
        union
        select 3 row_num,
               null str
          from dual
        union
        select 4 row_num,
               'c' str
          from dual) a;

select listagg(a.str, ',') within group(order by a.row_num) expression
  from (select 1 row_num,
               null str
          from dual
        union
        select 2 row_num,
               'a' str
          from dual
        union
        select 3 row_num,
               'b' str
          from dual
        union
        select 4 row_num,
               'c' str
          from dual) a;

Select allOpen in new window

ADDON:
You may want to do a "string2row" first ;-)

select single_element,
       element_no
  from (select trim(regexp_substr(str, '[^ '']+', 1, level)) as single_element,
               level as element_no
          from (select rownum as id,
                       trim(regexp_replace(upper('a,,' || 'b,' || null || ',c,,'),
                                           ',+',
                                           ' ')) str
                  from dual)
        connect by instr(str, ' ', 1, level - 1) > 0
               and id = prior id
               and prior dbms_random.value is not null);

Select allOpen in new window

:)  depends on the test cases used. e.g.
BY_DECODE      BY_CASE
a,b,c                     a,b,c
d,e,f                      d,e,f
g,h,i                      g,h,i
,j,k                        j,k
,l,m                       l,m
,o                          o
p                          p

select
     field1
  || decode(field2,null,null,',')
  || field2
  || decode(field3,null,null,',')
  || field3
  || decode(field4,null,null,',')
  || field4
  as by_decode

,    field1
  || case when field1 is not null and  coalesce(field2,field3,field4) is not null then ',' end
  || case when field2 is not null then field2 end
  || case when field2 is not null and  coalesce(field3,field4) is not null then ',' end
  || case when field3 is not null then field3 end
  || case when field3 is not null and  field4 is not null then ',' end
  || field4
  as by_case

from FIELDS
;


CREATE TABLE FIELDS
	("FIELD1" varchar2(10), "FIELD2" varchar2(10), "FIELD3" varchar2(10), "FIELD4" varchar2(10))
;

INSERT ALL 
	INTO fIELDs ("FIELD1", "FIELD2", "FIELD3", "FIELD4")
		 VALUES ('a', 'b', NULL, 'c')
	INTO fIELDs ("FIELD1", "FIELD2", "FIELD3", "FIELD4")
		 VALUES ('d', NULL, 'e', 'f')
	INTO fIELDs ("FIELD1", "FIELD2", "FIELD3", "FIELD4")
		 VALUES ('g', 'h', 'i', NULL)
	INTO fIELDs ("FIELD1", "FIELD2", "FIELD3", "FIELD4")
		 VALUES (null, 'j', 'k', NULL)
	INTO fIELDs ("FIELD1", "FIELD2", "FIELD3", "FIELD4")
		 VALUES (null, 'l', null, 'm')
	INTO fIELDs ("FIELD1", "FIELD2", "FIELD3", "FIELD4")
		 VALUES (null, null, null, 'o')
	INTO fIELDs ("FIELD1", "FIELD2", "FIELD3", "FIELD4")
		 VALUES ('p', null, null, null)
SELECT * FROM dual
;

Select allOpen in new window