[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

compare string with <>''

Posted on 2004-11-03
7
Medium Priority
?
24,235 Views
Last Modified: 2011-08-18
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
0
Comment
Question by:hernst42
7 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 12484344
Try this:

WHERE "Value" Is Not Null;
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 500 total points
ID: 12484400
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
 
LVL 6

Expert Comment

by:morphman
ID: 12484502
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 48

Author Comment

by:hernst42
ID: 12484700
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
 
LVL 6

Expert Comment

by:morphman
ID: 12484744
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
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 500 total points
ID: 12485051
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 12485341
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question