I often what to distinguish between NULL strings and those which are empty. Is there an easy way to do this?
asked
How can I tell the difference between NULL and an empty string?
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).
You can use the DUMP function to see if it is NULL oR space.
select DUMP(column_ in_question() from table
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.
