How To Only SELECT Rows With Non-zero And Non-null Columns Efficiently In Big Query?

I am having a table with large number of columns in Big Query. The table has lot of rows with some column values as 0/0.0 and null. For example Row A B C D E F 1

Solution 1:

Below example for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT "abc" a, 0 b, NULL c, "xyz" d, 0 e, 0.0 f UNION ALL
  SELECT "bcd", 1, 5, "wed", 4, 65.5 
)
SELECT *
FROM `project.dataset.table` t
WHERE NOT REGEXP_CONTAINS(TO_JSON_STRING(t), r':0[,}]|null[,}]')  

with output

Row a   b   c   d   e   f    
1   bcd 1   5   wed 4   65.5