sql query to find second not null value in a table (oracle)

Question

I have have a table which has n columns with n columns.Some of the column name are as follows

c, c2,c3,c4,c5  , c25

sample data
c1    c2     c3   c4  c5  (consider only 5 in this case)
x     y      z    z  y
x     y
x
a     b     j     k
a     c      g    h  i
k     l      m    n  o

Now op = second not null value from right side sample op for above data

z
x
x  (special case as no data left of x)
j
h
n 

Cannot use COALESCE ad i need second not null not first

Can someone help me with this query

Answer 1

You can do this with a more complex case statement:

select (case when c5 is not null
             then coalesce(c4, c3, c2, c1)
             when c4 is not null
             then coalesce(c3, c2, c1)
             when c3 is not null
             then coalesce(c2, c1)
             else c1
        end)
. . .

Answer 2

Something like:

 select nvl2(c5,c4,nvl2(c4,c3,nvl2(c3,c2,c1))) result
 from   my_table

Not tested.