How to replace all null values with 'N/A' in a sqlite table

Question

I have a big sqlite table and I want to output it into a file. So I want all the null value to be shown as string 'N/A' in the file. How to achieve this by sqlite query? Thanks.

Answer 1

You can use the ifnull function as:

select ifnull(mycolumn, 'N/A') from mytable;

From the sqllite.org (http://www.sqlite.org/lang_corefunc.html):

The ifnull() function returns a copy of its first non-NULL argument, or NULL if both arguments are NULL. Ifnull() must have exactly 2 arguments. The ifnull() function is equivalent to coalesce() with two arguments.

I don't think can do something like select ifnull(*, 'N/A') from mytable;, so you will have to list all the columns explicitly.

Here is something you can use to generate a csv output:

sqlite> .mode csv
sqlite> .output myoutput.csv
sqlite> select ifnull(mycolumn1, 'N/A'), ifnull(mycolumn2, 'N/A') from mytable;