compare string with <>''

Hi,
I'm searching in oracle 9.2i for a string with <>'' but I don't get the exprected result. e.g
Select * from mt_filearchivev WHERE "Value"<>'';
but I get no row, even there is a row with the text 'LOBylStringsyb100421-2' for "Value". Is this the expected behaviour or is it documented anywhere or a bug ??

When searching with "Value" is not null I get the expected row.

More info about the table structure:
CREATE TABLE mt_filearchivev (
    pk                               NUMBER                          NOT NULL
  , "FK_FileArchive"                 NUMBER                          NOT NULL
  , "Field"                          NUMBER                          NOT NULL
  , "Value"                          VARCHAR2 (4000)                
  , "Client"                         VARCHAR2 (31)                   DEFAULT USER NOT NULL
)

The content of the table:
# PK     FK_FileArchive Field  Value                  Client
= ====== ============== ====== ====================== ============
1 100000 100000         100082 LOBylStringsyb100421-2 HERNST230MC1
LVL 48
hernst42Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Try this:

WHERE "Value" Is Not Null;
0
seazodiacCommented:
This is expected behavior.

when you use literal string '' in oracle, it means NULL

but Oracle interpret any operation again NULL is NULL. so <> '' you will get NULL, not true or false.



so use IS NULL or IS NOT NULL instead.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
morphmanCommented:
ALso, might be an idea to compare to

trim(field) is not null;

As this will also exclude rows that have spaces in them for whatever reason.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

hernst42Author Commented:
So how would I use an empty string '' instead of a NULL-value. Any chance to distinguish an empty string and NULL in Oracle?
0
morphmanCommented:
an empty string is null, but in oracle you should use the null keyword.

see below:-


control@assist_o> create table dan(test varchar2(10));

Table created.

Elapsed: 00:00:00.02
control@assist_o> insert into dan values('');

1 row created.

Elapsed: 00:00:00.00
control@assist_o> insert into dan values(null);

1 row created.

Elapsed: 00:00:00.00

control@assist_o> select count(*) from dan where test = '';

       COUNT(*)
---------------
              0

Elapsed: 00:00:00.00
control@assist_o> select count(*) from dan where test is null;

       COUNT(*)
---------------
              2
0
johnsoneSenior Oracle DBACommented:
No, there is no way to distinguish between NULL and an empty string in Oracle.  To Oracle, they are the same.

This is a topic of much discussion.
0
paquicubaCommented:
In case you have a blankline I can tell you that blanklines cannot be treated as nulls:

SQL> create table test( c1 varchar2(2));

Table created.

Elapsed: 00:00:00.00
SQL> insert into test values( ' ');

1 row created.

Elapsed: 00:00:00.00
SQL> select rownum, c1 from test;

    ROWNUM C1
---------- --
         1

Elapsed: 00:00:00.00
SQL> select rownum, c1 from test where c1 is null;

no rows selected

Elapsed: 00:00:00.00
SQL> select rownum, c1 from test where c1 is not null;

    ROWNUM C1
---------- --
         1

Elapsed: 00:00:00.00
SQL> select rownum, c1 from test where trim(c1) is null;

no rows selected

Elapsed: 00:00:00.00

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.