14

I want to select a concatenation of a couple of fields, but with a separator between them. The separator should only be there if both operands are not null.

So for a record with a='foo', b=NULL, c='bar', I want to get the result abc='foo;bar' (not 'foo;;bar').

I would like to have a function like concat_sep(a, b, ';') that only adds the ';' inbetween if both a and b are not null.

Of course, I can use nvl2 like this:

select
  a, b, c, 
  substr(abc, 1, length(abc) - 1) as abc
from
  (select
    a, b, c, 
    nvl2(a, a || ';', '') || nvl2(b, b || ';', '') || nvl2(c, c || ';', '') as abc
  from
    Table1)

But as you can see, this code becomes cloggy soon, especially when you got more than 3 columns and you've given them sensible names instead of a, b and c. ;-)

I couldn't find a shorter, easier or more readable way, but I thought I'd ask here before giving up entirely (or waste time writing such a function myself).

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • seems like very specific logic you want: why would writing your own function yourself be a waste of time? – tbone Jul 12 '12 at 14:39
  • It would be if there turned out to be one already. :) – GolezTrol Jul 12 '12 at 14:59
  • without 11g listagg looks like you WILL need to write your own. And looking at your comments, it seems you DID write your own, so I'm confused, are you looking for some functionality your own function doesn't provide? Maybe a use case example to see how you plan to use this (I can think of a few approaches) – tbone Jul 12 '12 at 16:24
  • I don't actually need LISTAGG, I need a CONCAT function that allows me to specify a separator to put between two (or more) non-null values. @LukasEder suggested LISTAGG as a possible solution, which is a rather complex one, given my use case. So I do have my LISTAGG replacement, but it's not what I need now. – GolezTrol Jul 12 '12 at 19:26

4 Answers4

7

I know you're using 10g, so that won't work. But for completeness, LISTAGG() handles NULL values "correctly". For that you'd have to update to 11g2, though:

-- Some sample data, roughly equivalent to yours
with t as (
  select 'foo' as x from dual union all
  select null       from dual union all
  select 'bar'      from dual
)
-- Use the listagg aggregate function to join all values
select listagg(x, ';') within group (order by rownum)
from t;

Or a bit more succinct, if you want to list columns from a table:

-- I use SYS.ORA_MINING_VARCHAR2_NT as a TABLE TYPE. Use your own, if you prefer
select listagg(column_value, ';') within group (order by rownum)
from table(ORA_MINING_VARCHAR2_NT('foo', null, 'bar'));

Or against an actual table:

select listagg(column_value, ';') 
       within group (order by rownum)
from Table1
cross join table(ORA_MINING_VARCHAR2_NT(Table1.a, Table1.b, Table1.c))
group by Table1.id;

Now I'm not sure if this is so much better (more readable) than your original example :-)

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • It feels a bit icky to transform columns to a table and then aggregate that. I wanted to try it, at least as a learning example. :) Too bad ORA_MINING_VARCHAR2_NT also seems not to be available in 10g, which is a pity because, I've already written a LISTAGG replacement for 10g: http://stackoverflow.com/a/7885793/511529 – GolezTrol Jul 12 '12 at 14:57
  • Yes, icky is just the beginning. It used to be called `DMSYS.ORA_MINING_VARCHAR2_NT`. [This answer](http://stackoverflow.com/a/8786893/521799) shows how you can find another `SYS` table/varray type that could suit your needs – Lukas Eder Jul 12 '12 at 14:58
  • 2
    Also doesn't work. Apparently I haven't got the data mining features yet in my database. I'll keep it in mind, but I got a feeling I shouldn't use this solution in production code anyway. ;-) – GolezTrol Jul 12 '12 at 15:05
  • Yes, please, don't. At least for the sake of your fellow programmers :-) – Lukas Eder Jul 12 '12 at 15:06
  • I've used my own nvl2 solution, which actually looks quite nice if I put all nvl2s on a separate line. Nevertheless, I happily accept this answer. It doesn't work for me, but it probably will if you got the right database for it. And it made me remember to think out of the box even more often. :) – GolezTrol Jul 13 '12 at 20:23
7
select trim(';' from REGEXP_REPLACE (a || ';' || b || ';' || c , ';+' , ';')) abc 
from Table1
Edoardo
  • 71
  • 1
  • 2
  • 2
    Hey welcome to SO, would be good to elaborate on your answer: https://stackoverflow.com/help/how-to-answer – RK1 Apr 30 '19 at 11:05
2

AFAIK, there's no succinct way to do this.

In the past, I've resorted to

SELECT a
||     DECODE(b
       ,      NULL, NULL
       ,      ';' || b)
||     DECODE(c
       ,      NULL, NULL
       ,      ';' || c)
||     DECODE(d
       ,      NULL, NULL
       ,      ';' || d)
...
FROM   table1

but that's no better than your example.

Tebbe
  • 1,372
  • 9
  • 12
0

Not all fields will have data sequentially. This is for fields that are blank and without spaces:

  1. Evaluate if the field is blank, if so concatenate an empty field so it is ignored and if blank, concatenate the field with a space on the end.

  2. Then [TRIM] spaces (leading and trailing), then [REPLACE] spaces with your delimiter.

You can get creative with this until Oracle gives a function to do this.

Code snippet:

SELECT 
    f1,f2,f3,f4
  , REPLACE (TRIM(
     CASE WHEN f1 IS NULL THEN '' ELSE f1||' ' END||
     CASE WHEN f2 IS NULL THEN '' ELSE f2||' ' END||
     CASE WHEN f3 IS NULL THEN '' ELSE f3||' ' END||
     CASE WHEN f4 IS NULL THEN '' ELSE f4||' ' END),' ','|') concat_result
from 
    (SELECT 
         'I' f1
        ,'want' f2
        ,'' f3
        ,'concat' f4
 FROM dual) c'
Deatrice Ramos
  • 1