question

Theo Spears avatar image
Theo Spears asked

How can I tell the difference between NULL and an empty string?

I often what to distinguish between NULL strings and those which are empty. Is there an easy way to do this?

stringnull
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Bick avatar image
David Bick answered

In Oracle a zero length varchar gets converted to NULL... it's no longer an empty string.

Say you have a table:

create table customers
( customer_id number(9), customer_name varchar2(100) );

And you insert a record:

insert into customers ( customer_id, customer_name ) values ( 1, '' );

If you try to retrieve the record using an empty string:

select * from customers where customer_name = '';

...then you get nothing back.

Whereas if you try:

select * from customers where customer_name is null;

...then you get back the record you inserted.

(Note that things are a little different if you're playing with char variables in PL/SQL. See this Ask Tom article for more details: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5984520277372).

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tikey avatar image
Tikey answered

You can use the DUMP function to see if it is NULL oR space.

select DUMP(column_ in_question() from table

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leigh Riffel avatar image
Leigh Riffel answered

No, there is no easy way to distinguish these as Oracle does not maintain the difference. You can store a special value such as chr(0) in the field to mean an empty string or you can keep a separate column to indicated whether the null value indicates NULL or empty string.

In my use the desire to know the difference between NULL and an empty string was usually an attempt to know if the data was still at it's initial state as opposed to once containing a value and having it removed. If this is the problem it can be solved several other ways such as maintaining a modified flag, last updated date, or previous value field. A trigger could be employed to update these values.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.