how to check whether a field has a value in Oracle

Question

hi,

How can I check whether there is a value set for a particular field in a table ?

Thank You

Answer 1

you can use this query,

select * from table_name where column_name='field value';

Answer 2

.... WHERE field IS NOT NULL ....

Answer 3

This way:

SELECT whatever FROM table WHERE that_field IS NULL

Or replace IS NULL with IS NOT NULL if you want to select rows where the field has a value.

Answer 4

We can run a query checking on NOT NULL in the WHERE clause. I'm using count for convenience.

SQL> select count(*)
  2  from emp
  3  where comm is not null
  4  /

  COUNT(*)
----------
         4

SQL>

So that's four rows where COMM has a value set.

If we want to test for the presence of a value in the projection, then we can use CASE(), DECODE() or one of Oracle's NULL-related functions. For instance, this statement wraps a call to [NVL2()][2] in a SUM() to count how many instances of COMM are NULL and NOT NULL in the same query.

SQL> select sum(nvl2(comm, 1, 0)) as notnull_cnt
  2         , sum(nvl2(comm, 0, 1)) as null_cnt
  3  from emp
  4  /

NOTNULL_CNT   NULL_CNT
----------- ----------
          4         16

SQL>