how to handle null while concatenate using || in sqlite3

Question

I am concatenating the three column, but getting empty value , as one of three column is null sometime. how to handle null column value while concatenating using || operator code : >> select (t1.ab || t1.cd || t1.ef) as test from source output is empty when any one of them is null, otherwise it works fine

Answer 1

There are 2 functions that you can use COALESCE() and IFNULL():

select coalesce(t1.ab, '') || coalesce(t1.cd, '') || coalesce(t1.ef, '') as test 
from source

or:

select ifnull(t1.ab, '') || ifnull(t1.cd, '') || ifnull(t1.ef, '') as test 
from source