sqlite: how to find all rows in a table where ANY of the columns are null?

Question

I have a very large CSV file that I imported into a sqlite table. There are over 50 columns and I'd like to find all rows where any of the columns are null. Is this even possible? I'm just trying to save myself the time of writing out all of the 50 different columns in a where clause. Thanks.

Answer 1

It's an interesting question but it's a probably quicker to write a quick script that generates your converts that copy/pasted header row from your CSV to the appropriate script.

For instance this works in LINQPad (C#)

void Main()
{
 string input = "adasda|sadasd|adasd|";
 char delim = '|';
 StringBuilder sql = new StringBuilder();
 sql.AppendLine("SELECT * FROM table WHERE ");
 foreach (string s in input.Split(delim))
 {
     if (!String.IsNullOrEmpty(s))
    sql.Append(s).AppendLine(" IS NULL OR ");
 }
 sql.ToString().Trim('\r', '\n', 'O', 'R',' ').Dump();
}

Answer 2

No. Not without a cursor using DESCRIBE TABLE or an intermediate technology.

Your best bet would be to DEFAULT NULL the columns and re-import the data. But depending on the CSV import and column types, you may still get empty values in the columns.

Sucks, but it is probably quicker to just copy and paste the SQL commands. The script would be reusable.