3

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!

JYX
  • 2,653
  • 2
  • 17
  • 15
  • 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: http://stackoverflow.com/questions/1268177/oracle-not-distinguishing-between-nulls-and-empty-strings?rq=1 – Alvaro Rodriguez Jun 06 '14 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 Jun 06 '14 at 16:44
  • possible duplicate of [Why does Oracle 9i treat an empty string as NULL?](http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null) – johnny Jun 06 '14 at 16:45
  • 1
    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 Jun 06 '14 at 16:46
  • @user2672165 has it correct, although I would probably reverse the order to where (name is not null or name <> '') – Mike Jun 06 '14 at 17:14
  • I don't think that'll work, I need (name <> '' and name is not null) for SQL Server but just (name is not null) for Oracle. – JYX Jun 06 '14 at 17:53

2 Answers2

6

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

user2672165
  • 2,986
  • 19
  • 27
  • I think that explains it, thanks. I also found this: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm 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. – JYX Jun 06 '14 at 17:55
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 '')

Mike
  • 3,186
  • 3
  • 26
  • 32
  • @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. – Mike Jun 06 '14 at 17:28
  • So is `''` a zero length string? – mason Jun 06 '14 at 17:29
  • @mason Yes, if there are no characters between the quotes, then it is a zero length string – Mike Jun 06 '14 at 17:30
  • 2
    **But** you said in your answer that `''` is not treated as NULL, but that zero length strings are NULL. Which is it? – mason Jun 06 '14 at 17:33
  • @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; – Mike Jun 06 '14 at 17:47