CODEWITHSUNDEEP
sqlsqlite
goodking
goodking

Reputation: 145

sqlite get first not null column data and index within the record?

Having a table, consisting of several columns of text type like,

CREATE TABLE SomeTable(
    COL1      TEXT,
    COL2      TEXT,
    COL3      TEXT,
    COL4      TEXT,
    ...)

only one of which is not null within one row, that is each record in the table looks like:

NULL, NULL, NULL, SomeText, NULL, ...

I need to get the data and the index of the first not null column?

Since standart says that "Adding anything to null gives null", I'm thinking of using coalesce function to concatenate index of the column with its data

select coalesce('1 ' || COL1, '2 ' || COL2, '3 ' || COL3, ...) col from SomeTable;

Is there a better way to accomplish this task?

Upvotes: 0

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

I think case is the simplest method:

select (case when col1 is not null then '1 ' || col1 
             when col2 is not null then '2 ' || col2
             . . .
        end) as col

Upvotes: 1

Related Questions