How to count null columns in a row?

Question

How to count all the columns in a table that have a null value?

The table having a large number of columns and the method should iterate over the columns in a dynamic manner.

In any given row (selected by an identifier), count the null cells.

Select count(number of null value cells) where id=1 from table

e.g:

I have a table consisting of 200 columns I want to know how many null cells does the row with id=1 have

Possible duplicate of [How do I list all the columns in a table?](http://stackoverflow.com/questions/1580450/how-do-i-list-all-the-columns-in-a-table) — Juan Carlos Oropeza, Oct 14 '16 at 13:21
This is some what similar [Search All Fields In All Tables For A Specific Value (Oracle)](http://stackoverflow.com/questions/208493/search-all-fields-in-all-tables-for-a-specific-value-oracle) — Pரதீப், Oct 14 '16 at 13:26
You want to count number of nulls in each row or you want to have number of nulls in each column? — Kacper, Oct 14 '16 at 13:35
What does *column has a null value* mean? That it has at least one null in any row? Or null in all rows? — pid, Oct 14 '16 at 13:44
in any given row (selected by an identifier), count the null cells — aurelius, Oct 14 '16 at 14:23

score 1 · Answer 1 · answered Oct 14 '16 at 14:45

Basically, you need to check every column in a selected row if it's null or not. Since you have 200+ columns in your table, manual approach seems tedious, so you can automate it a little bit and construct the query dynamically by querying user_tab_columns:

-- set up

create table t1(
  rid number primary key,
  c1 varchar2(17),
  c2 date,
  c3 timestamp,
  c4 number
);
insert into t1
  valueS(1, 'string', null, systimestamp, null);
  commit ;

-- going to use DECODE function - doesnt require type consistency.
select 'decode('||column_name||', null, 1, 0)+' as res
  from user_tab_columns
 where table_name = 'T1'

Result:

RES  
------------------------------
decode(RID, null, 1, 0)+
decode(C1, null, 1, 0)+ 
decode(C2, null, 1, 0)+     
decode(C3, null, 1, 0)+ 
decode(C4, null, 1, 0)+

And final query:

select decode(C4, null, 1, 0)+
       decode(C3, null, 1, 0)+
       decode(C2, null, 1, 0)+
       decode(C1, null, 1, 0)+ 
       decode(RID, null, 1, 0) as num_of_nulls
  from t1
 where rid = 1

Result:

 NUM_OF_NULLS
--------------
             2

score 1 · Answer 2 · answered Oct 14 '16 at 14:56

Try this. You can pass any ID and get the number of columns with NULL value.

CREATE TABLE TEST (ID NUMBER, B VARCHAR2(20), C NUMBER, D VARCHAR2(200));
INSERT INTO TEST VALUES (1,NULL,NULL,'XX');

SELECT COUNT(NULL_COLS)
FROM (
SELECT 
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT CASE WHEN '||COLUMN_NAME||' IS NULL THEN 0 ELSE NULL END COL_VAL FROM '||TABLE_NAME||' WHERE ID=&VALUE')),'/ROWSET/ROW/COL_VAL')) NULL_COLS
FROM   USER_TAB_COLUMNS
WHERE  TABLE_NAME='TEST');

How to count null columns in a row?

2 Answers2