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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can not count nulls  at least not in Oracle   Instead try this  SELECT count 1  FROM TABLE WHERE COL NAME IS NULL

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT COUNT  COL NAME   FROM TABLE  WHERE TRIM  COL NAME  IS NULL  or COL NAME  NULL

User · Answer

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   gt  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

[oracle] NULL or BLANK fields (ORACLE)

Examples related to oracle

Examples related to null