Solved

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

Posted on 2013-05-23
11
2,070 Views
Last Modified: 2013-05-31
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
Comment
Question by:damixa
11 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39192336
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
 

Author Comment

by:damixa
ID: 39192391
I was hoping there was a better solution than replacing
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39192411
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39193067
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39193105
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39193108
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39193110
>>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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39193125
:) agreed, but I enjoyed the exercise (it does produce the desired result as far as I can tell)
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39193664
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
 
LVL 31

Accepted Solution

by:
awking00 earned 500 total points
ID: 39202671
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39212315
:)  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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now