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
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.
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.
You can not count nulls (at least not in Oracle). Instead try this
SELECT count(1) FROM TABLE WHERE COL_NAME IS NULL
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).
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.
SELECT COUNT (COL_NAME)
FROM TABLE
WHERE TRIM (COL_NAME) IS NULL
or COL_NAME='NULL'
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
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.
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!
Source: Stackoverflow.com