Select fields not equal to the empty string in oracle

Question

I'm trying to write a query that simply selects all non-empty names. Both the following queries return no results:

 SELECT name FROM MyTable WHERE name != '';

 SELECT name FROM MyTable WHERE name = '';

For context, both of these queries do return results:

 SELECT name FROM MyTable WHERE name != 'a';

 SELECT name FROM MyTable WHERE name IS NOT NULL;

I read somewhere that the empty string is equivalent to NULL in oracle, but I still don't see why that explains this behaviour. I need to support both SQL Server and Oracle which is why I can't just rely on WHERE name IS NOT NULL

Can anyone explain what's happening here? Thanks!

Hi friend, this depends a bit on the type of your column. Oracle behaves a bit unexpectedly on varchar2 vs char columns. This other question has details: stackoverflow.com/questions/1268177/… — Alvaro Rodriguez
– Alvaro Rodriguez, Commented Jun 6, 2014 at 16:42
I don't know your app and support and how you do it, but this might be a task for and ORM? It would be interesting to see if it helped. — johnny
– johnny, Commented Jun 6, 2014 at 16:44
possible duplicate of Why does Oracle 9i treat an empty string as NULL? — johnny
– johnny, Commented Jun 6, 2014 at 16:45
To support both SQL Server and Oracle why don't you use (name!='' or name is not null)? Provided that each condition supports one database. — user2672165
– user2672165, Commented Jun 6, 2014 at 16:46
@user2672165 has it correct, although I would probably reverse the order to where (name is not null or name <> '') — Mike
– Mike, Commented Jun 6, 2014 at 17:14

user2672165 · Accepted Answer · 2014-06-06 17:29:03Z

6

Any comparison that involves a NULL value will always return FALSE.

answered Jun 6, 2014 at 17:29

user2672165

3,05921 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JYX Over a year ago

I think that explains it, thanks. I also found this: docs.oracle.com/cd/B19306_01/server.102/b14200/… which says "Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null" which explains why name is neither equal nor not-equal to null.

Bacon Bits Over a year ago

Any comparison that involves NULL returns UNKNOWN, not FALSE. However, a SELECT statement only returns a record when the WHERE clause is TRUE, which neither FALSE nor UNKNOWN is.

Mike · Accepted Answer · 2014-06-06 17:22:55Z

4

Can anyone explain what's happening here? Thanks!

From the Ask Tom archive.

A ZERO length varchar is treated as NULL.

'' is not treated as NULL.

'' when assigned to a char(1) becomes ' ' (char types are blank padded strings).

'' when assigned to a varchar2(1) becomes '' which is a zero length string and a zero length string is NULL in Oracle (it is no longer '')

answered Jun 6, 2014 at 17:22

Mike

3,3113 gold badges31 silver badges35 bronze badges

5 Comments

Mike Over a year ago

@mason Essentially when you insert a zero length string into a Varchar2 column, it becomes a NULL in Oracle. This goes against what the ANSI standard reads, but Oracle has been doing their own thing forever.

mason Over a year ago

So is '' a zero length string?

Mike Over a year ago

@mason Yes, if there are no characters between the quotes, then it is a zero length string

mason Over a year ago

But you said in your answer that '' is not treated as NULL, but that zero length strings are NULL. Which is it?

Mike Over a year ago

@mason Was a cut/paste job from the Article, there are no zero length strings in Oracle varchars. You can do a quick test of that with: DECLARE A VARCHAR(2); BEGIN A := ''; IF A = '' THEN dbms_output.put_line('Empty String'); ELSE dbms_output.put_line('It''s NULL'); END IF; IF '' = '' THEN dbms_output.put_line('Empty String'); ELSE dbms_output.put_line('It''s NULL'); END IF; END;

Collectives™ on Stack Overflow

Select fields not equal to the empty string in oracle

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related