Oracle: Concat avec séparateur, mais seulement si les deux opérandes ne sont PAS NULL

Je veux sélectionner une concaténation d'un couple de champs, mais avec un séparateur entre eux. Le séparateur doit uniquement si les deux opérandes ne sont pas null.

Donc, pour un enregistrement avec a='foo', b=NULL, c='bar', je veux obtenir le résultat abc='foo;bar' (pas 'foo;;bar').

Je voudrais avoir une fonction comme concat_sep(a, b, ';') qui n'ajoute le ';' dans l'intervalle, si a et b sont non nuls.

Bien sûr, je peux utiliser nvl2 comme ceci:

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)

Mais comme vous pouvez le voir, ce code devient cloggy tôt, surtout quand vous avez obtenu plus de 3 colonnes et vous leur avez donné sensible des noms au lieu de a, b et c. 😉

Je ne pouvais pas trouver un plus court, plus facile et plus lisible, mais j'ai pensé que je voudrais vous demander ici avant d'abandonner entièrement (ou perdre du temps à écrire une telle fonction moi-même).

semble très logique spécifique que vous souhaitez: pourquoi écrire votre propre fonction de vous-même être une perte de temps?
Il serait si il s'est avéré être déjà un. 🙂
sans 11g listagg ressemble, vous devez écrire votre propre. Et en regardant vos commentaires, il semble que vous N'écrivez votre propre, donc je suis confus, vous êtes à la recherche pour certaines fonctionnalités de votre propre fonction ne fournit pas? Peut-être un cas d'utilisation exemple pour voir comment vous prévoyez de l'utiliser (je ne peux penser à quelques approches)
Je n'ai pas vraiment besoin LISTAGG, j'ai besoin d'une fonction CONCAT qui me permet de spécifier un séparateur entre deux (ou plus) les valeurs non null. @LukasEder suggéré LISTAGG comme une solution possible, ce qui est assez complexe, étant donné mon cas d'utilisation. J'ai donc mon LISTAGG de remplacement, mais ce n'est pas ce dont j'ai besoin maintenant.

OriginalL'auteur GolezTrol | 2012-07-12

  1. 6

    Je sais que vous êtes à l'aide de 10g, donc qui ne fonctionne pas. Mais pour être complet, LISTAGG() poignées NULL valeurs "correctement". Pour cela vous devrez donc mettre à jour à 11g2:

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

    Ou un peu plus succincte, si vous voulez la liste des colonnes d'une 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'));

    Ou contre une table réelle:

    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;

    Maintenant, je ne suis pas sûr si c'est tellement mieux (plus lisible) que votre exemple d'origine 🙂

    Il se sent un peu dégueulasse pour transformer les colonnes d'une table et d'agrégats. J'ai voulu l'essayer, au moins comme un exemple d'apprentissage. 🙂 Trop mal ORA_MINING_VARCHAR2_NT semble également ne pas être disponibles dans 10g, ce qui est dommage parce que, j'ai déjà écrit une LISTAGG de remplacement pour 10g: stackoverflow.com/a/7885793/511529
    Oui, le problème est que le début. Il a utilisé pour être appelé DMSYS.ORA_MINING_VARCHAR2_NT. Cette réponse montre comment vous pouvez trouver un autre SYS table/varray type qui peut répondre à vos besoins
    Aussi ne fonctionne pas. Apparemment, je n'ai pas les fonctionnalités d'exploration de données dans ma base de données. Je vais garder cela à l'esprit, mais j'ai eu un sentiment que je ne devriez pas utiliser cette solution dans le code de production de toute façon. 😉
    Oui, s'il vous plaît, ne le faites pas. Au moins pour le plaisir de vos collègues programmeurs 🙂
    J'ai utilisé mon propre nvl2 solution, qui semble effectivement assez bien si j'ai mis tous les nvl2s sur une ligne distincte. Néanmoins, je suis heureux d'accepter cette réponse. Il ne fonctionne pas pour moi, mais il ne sera probablement si vous avez le droit de base de données. Et cela m'a fait penser à penser hors de la boîte, même plus souvent. 🙂

    OriginalL'auteur Lukas Eder

  2. 1

    Autant que je sache, il n'y a aucune manière succincte pour ce faire.

    Dans le passé, j'ai eu recours à

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

    mais ce n'est pas mieux que votre exemple.

    En effet, c'est à peu près la même. Merci néanmoins.

    OriginalL'auteur Tebbe