NULL or BLANK fields (ORACLE)

Question

I'm new to Oracle, so my question might sound silly. I did go through the previous posts, but no luck. In the table, there is a column which is blank, and i am trying to find out the blank count in the column. I tried:

SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME IS NULL SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME = ' ' SELECT COUNT (COL_NAME) FROM TABLE WHERE TRIM (COL_NAME)= ' ' 

The result to all the queries above is 0

However, when i did

SELECT COL_NAME DUMP (COL_NAME,1016) FROM TABLE 

gave me:

COL_NAME       DUMP (COL_NAME,1016)                NULL                NULL                NULL 

and so on..

But there are hundreds or thousands of blank fields/empty fields in that column. Can anyone please help me to find count of those blank/empty fields in that column ? I am using Toad for Oracle 9.0.1.8

Answer 1

COUNT(expresion) returns the count of of rows where expresion is not null. So SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME IS NULL will return 0, because you are only counting col_name where col_name is null, and a count of nothing but nulls is zero. COUNT(*) will return the number of rows of the query:

SELECT COUNT (*) FROM TABLE WHERE COL_NAME IS NULL 

The other two queries are probably not returning any rows, since they are trying to match against strings with one blank character, and your dump query indicates that the column is actually holding nulls.

If you have rows with variable strings of space characters that you want included in the count, use:

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

trim(COL_NAME) will remove beginning and ending spaces. If the string is nothing but spaces, then the string becomes '' (empty string), which is equivalent to null in Oracle.