CODEWITHSUNDEEP
oracle-database
nimo
nimo

Reputation: 229

how to check whether a field has a value in Oracle

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

Thank You

Upvotes: 1

Views: 8799

Answers (4)

APC
APC

Reputation: 146199

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>

Upvotes: 1

petersohn
petersohn

Reputation: 11720

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.

Upvotes: 0

Ingo
Ingo

Reputation: 36329

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

Upvotes: 3

professionalcoder2010
professionalcoder2010

Reputation: 367

you can use this query,

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

Upvotes: 0

Related Questions