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.

Answer 2

A NULL column is not countable, however a row that has a NULL column is. So, this should do what you're looking for:

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

Note, that there are non-printing characters that this will not address. For example U+00A0 is the non-breaking space character and a line containing that will visually appear empty, but will not be found by the tests above.

Answer 3

So I just wondered about the same, but had at the same time had a solution to this. I wanted a query that included all rows in the table and counting both blanks and non blanks. So I came up with this.

SELECT COUNT(col_name)                                 VALUE_COUNT
       COUNT(NVL(col_name, 'X') - COUNT(col_name)      NULL_VALUE_COUNT
FROM   table
[CONDITIONS]

Instead of the NVL function you can count the primary key column to obtain the total count of rows

It works like a charm

Answer 4

You can not count nulls (at least not in Oracle). Instead try this

SELECT count(1) FROM TABLE WHERE COL_NAME IS NULL

Answer 5

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

Answer 6

DROP TABLE TEST;  -- COMMENT THIS OUT FOR THE FIRST RUN

CREATE TABLE TEST
(
    COL_NAME,
    TEST_NAME
) AS
(
    SELECT      NULL,         'ACTUAL NULL'  FROM DUAL
    UNION ALL
    SELECT      '',           'NULL STRING'  FROM DUAL
    UNION ALL
    SELECT      ' ',          'SINGLE SPACE' FROM DUAL
    UNION ALL
    SELECT      '  ',         'DOUBLE SPACE' FROM DUAL
    UNION ALL
    SELECT      '          ', 'TEN SPACES'   FROM DUAL
    UNION ALL   
    SELECT      'NONSPACE',   'NONSPACES'    FROM DUAL
)
;

SELECT LENGTH(COL_NAME) NUM_OF_SPACES, TEST_NAME
FROM TEST
WHERE LENGTH(COL_NAME) > 0         -- THERE IS SOMETHING IN THE FIELD
  AND TRIM(COL_NAME) IS NULL;      -- WHICH EQUATES TO NULL

table TEST dropped.
table TEST created.
NUM_OF_SPACES TEST_NAME

---

         1 SINGLE SPACE 
         2 DOUBLE SPACE 
        10 TEN SPACES

Once you have identified the columns that contain blanks, wrap that query in a count. If you actually need to identify the fields for some kind of update, consider selecting the ROWID as well.

Answer 7

One should NEVER treat "BLANK" and NULL as the same.

Back in the olden days before there was a SQL standard, Oracle made the design decision that empty strings in VARCHAR/ VARCHAR2 columns were NULL and that there was only one sense of NULL (there are relational theorists that would differentiate between data that has never been prompted for, data where the answer exists but is not known by the user, data where there is no answer, etc. all of which constitute some sense of NULL). By the time that the SQL standard came around and agreed that NULL and the empty string were distinct entities, there were already Oracle users that had code that assumed the two were equivalent. So Oracle was basically left with the options of breaking existing code, violating the SQL standard, or introducing some sort of initialization parameter that would change the functionality of potentially large number of queries. Violating the SQL standard (IMHO) was the least disruptive of these three options.

Oracle has left open the possibility that the VARCHAR data type would change in a future release to adhere to the SQL standard (which is why everyone uses VARCHAR2 in Oracle since that data type's behavior is guaranteed to remain the same going forward).

Answer 8

Try the nvl function. select count(nvl(col_name,0)) from table.

Sorry I reread the OP. What is the table structure? is the column varchar or char as that will make a difference?

try

select count(col_name), distinct(col_name) from table group by distinct(col_name)  

/can't remember if you need distinct in the group by but I think not/

and see if it gives you a return with a column name that is blank.

Answer 9

First, you know that "blank" and "null" are two COMPLETELY DIFFERENT THINGS? Correct?

Second: in most programming languages, "" means an "empty string". A zero-length string. No characters in it.

SQL doesn't necessarily work like that. If I define a column "name char(5)", then a "blank" name will be " " (5 spaces).

It sounds like you might want something like this:

select count(*) from my_table where Length(trim(my_column)) = 0;

"Trim()" is one of many Oracle functions you can use in PL/SQL. It's documented here:

http://www.techonthenet.com/oracle/functions/trim.php

'Hope that helps!