CODEWITHSUNDEEP
sqlitenull
user2561254
user2561254

Reputation: 69

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

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.

Upvotes: 5

Views: 12944

Answers (1)

vee
vee

Reputation: 38645

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;

Upvotes: 12

Related Questions