Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3011
  • Last Modified:

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

I have a query where I need to calculate a field as a concatenation of 4 field separated by comma. However I do need to not show null values. I can use coalesce, but I get multiple commas

so if

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

I need the result to be "a, b, c"

and not "a, b, , c"

thanks
0
damixa
Asked:
damixa
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
0
 
damixaAuthor Commented:
I was hoping there was a better solution than replacing
0
 
johnsoneSenior Oracle DBACommented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PortletPaulCommented:
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
;

Open in new window

0
 
PortletPaulCommented:
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

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
>>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...
0
 
PortletPaulCommented:
:) agreed, but I enjoyed the exercise (it does produce the desired result as far as I can tell)
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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;

Open 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);

Open in new window

0
 
awking00Commented:
SQL> select * from fields;

F F F F
- - - -
a b   c
d   e f
g h i

SQL> select field1||decode(field2,null,null,',')
  2       ||field2||decode(field3,null,null,',')
  3       ||field3||decode(field4,null,null,',')
  4       ||field4 fields
  5  from fields;

FIELDS
-------
a,b,c
d,e,f
g,h,i
0
 
PortletPaulCommented:
:)  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
;

Open in new window

0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now