Forum Stats

  • 3,728,534 Users
  • 2,245,647 Discussions
  • 7,853,586 Comments

Discussions

How to find only Null value record fields in a table

User_UMJZ5
User_UMJZ5 Member Posts: 159 Blue Ribbon

Hi All,

I have one table with more than 200 columns and millions of records but i want to check list of fields which are having only nulls,.if i use below query will get result but need to add all columns in where clause is there any way to find nulls.

Select * from table

where col1 is null or col2 is null


Thx

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,322 Red Diamond
    edited March 18

    Hi,

    need to add all columns in where clause is there any way to find nulls

    Only what you said: add all the columns in the WHERE clause. There are different ways to do it, but they all involve naming each individual column. You posted one way to find if any one (or more) column is NULL:

    WHERE  col1 IS NULL
    OR     col2 IS NULL
    OR     ...
    

    If all the columns have the same data type, you can also use

    WHERE  LEAST (col1, col2, ...)  IS NULL
    

    which is slightly shorter, but you still have to name each individual column.

Sign In or Register to comment.