NULL or BLANK fields check in Oracle

Posted on by

If you want a query which returns rows in column containing NULL or empty space then the using NULL alone in where clause will not return empty space rows. So the query should be altered as below

Actual Query

1
2
3
SELECT COUNT (*)
  FROM TABLE
 WHERE COL_NAME IS NULL;

Altered Query

1
2
3
SELECT COUNT (*)
  FROM TABLE
 WHERE trim(COL_NAME) IS NULL

(or)

Altered Query

1
2
3
4
SELECT COUNT (*)
  FROM TABLE 
  WHERE COL_NAME IS NULL OR
        LENGTH(TRIM (COL_NAME)) = 0

Comments are closed.