asked on

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

dsacker

Try this:

WHERE "Value" Is Not Null;

ASKER CERTIFIED SOLUTION

seazodiac

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

morphman

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.

hernst42

ASKER

So how would I use an empty string '' instead of a NULL-value. Any chance to distinguish an empty string and NULL in Oracle?

morphman

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

SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

paquicuba

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

compare string with &lt;&gt;''

compare string with <>''