KnowledgeHub
Questions
Tags
Users
Search
Alex Rivera
|
Logout
Edit Question
Title
Body
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).
Tags (comma-separated)
Save Edits
Cancel