You Asked
Hi Tom,
I have 5 columns. I need to select the first two not null values. I used COALESCE (c1,c2,c3,c4,c5) which would give the first not null value.
I moved to variable var1.
IF NULLIF(var1,c1) then COALESCE (c2,c3,c4,c5) to get second not null.
else if NULLIF(var1,c2) then COALESCE (c3,c4,c5) to get second not null.
and so on.
Is there any other way to get the second not null values from the list of five columns.
Thank you,
Priya
and Connor said...
You could use a little bit of regexp
<code>
SQL> with t as
2 (
3 select 11 c1, 12 c2, 13 c3, 14 c4, 15 c5 from dual union all
4 select 11 c1, null c2, null c3, 14 c4, 15 c5 from dual union all
5 select null c1, null c2, 13 c3, 14 c4, 15 c5 from dual union all
6 select null c1, 12 c2, null c3, 14 c4, 15 c5 from dual
7 )
8 select c1,c2,c3,c4,c5,
9 regexp_substr(
10 ','||c1||','||c2||','||c3||','||c4||','||c5||',',
11 ',([[:alnum:]]+)',1,1,'i',1) elem1,
12 regexp_substr(
13 ','||c1||','||c2||','||c3||','||c4||','||c5||',',
14 ',([[:alnum:]]+)',1,2,'i',1) elem2
15 from t;
C1 C2 C3 C4 C5 ELEM1 ELEM2
---------- ---------- ---------- ---------- ---------- ------------------------------ ---------
11 12 13 14 15 11 12
11 14 15 11 14
13 14 15 13 14
12 14 15 12 14
<code>
Is this answer out of date? If it is, please let us know via a Comment