CODEWITHSUNDEEP
sqlitenull
Richard Herron
Richard Herron

Reputation: 10112

How to not SELECT rows that contain NULL entries, or fill the NULL with 0 or NA, in SQLite

I use SQLite with R to store data that are too large for RAM. This allows me to SELECT and JOIN outside of RAM. My data are in general well-groomed, but sometimes there are null entries and I have no prior on where these null entries might be (nor do I care, I am happy to omit those rows).

Is there a way that I can not SELECT or return rows with null entries without specifically testing each column? Or replace all null entries with 0 or NA? Thanks!

Upvotes: 0

Views: 2453

Answers (2)

stian.net
stian.net

Reputation: 3973

Try the COALESCE function:

SELECT COALESCE(myColumn, 'valueIfNull')
FROM Table

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 839264

Is there a way that I can not SELECT or return rows with null entries without specifically testing each column?

To do this you need to test every column to see if it is NULL.

However if you don't like writing WHERE col1 IS NOT NULL AND col2 IS NOT NULL AND ... then you could try using coalesce or if all your columns have the same type you could try max:

SELECT col1, col2, col3
FROM yourtable
WHERE max(col1, col2, col3) IS NOT NULL

Upvotes: 3

Related Questions