Oracle empty string/NULL

Question

I have to write an SQL SELECT statement which is valid for MS SQL Server and Oracle. I have to work with old customer databases, so I cannot change anything in the db design...

Problem is that the table has a nullable column 'project' which is a varchar that is in some cases filled with an empty string - which is translated by oracle to NULL. So how do I select all columns that have an NON empty 'project'?

WHERE project IS NOT NULL                   works for oracle
WHERE project <> ''                         works for MS
WHERE project IS NOT NULL AND project <> '' works for MS, but not for Oracle

Thanks, Marko

It's like writing a code statement that is valid in both Java and C++ :) — Kombajn zbożowy
– Kombajn zbożowy, Commented Nov 19, 2013 at 11:47

ypercubeᵀᴹ · Accepted Answer · 2013-11-18 09:07:48Z

7

Because the condition '' = '' will be true only in SQL-Server (it's equivalent to '' IS NOT NULL) and the condition '' IS NULL will be true only in Oracle, you can use this:

WHERE ( project > '' AND '' = '')              -- for SQL-Server
   OR ( project IS NOT NULL AND '' IS NULL)    -- for Oracle

Note that if you have values that are only spaces, they will be treated differently between SQL-Server and Oracle. Test SQL-Fiddle-1 (Oracle) and SQL-Fiddle-2 (SQL-Server).

edited Nov 18, 2013 at 9:07

answered Nov 18, 2013 at 8:42

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

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

Comments

Ben · Accepted Answer · 2013-11-18 08:46:22Z

2

You can use NULLIF(), which is available in both SQL Server and Oracle (it's part of the ANSI standard).

select *
  from table
 where nullif(project, '') is not null

It works because Oracle evaluates the empty string to NULL. It's worth noting that Oracle does not evaluate NULLIF() if the first expression is NULL, but it does work this way round.

answered Nov 18, 2013 at 8:46

Ben

53.1k36 gold badges133 silver badges156 bronze badges

Comments

lordkain · Accepted Answer · 2013-11-18 09:05:48Z

-2

facts:

Oracle translate empty to NULL
SQL Server does not translate.

So if you have a Oracle and SQL Server database with same content, the result is the same, something you really want

where (project is not null OR project <> '')

edited Nov 18, 2013 at 9:05

user330315

answered Nov 18, 2013 at 8:39

lordkain

3,1191 gold badge16 silver badges18 bronze badges

3 Comments

ypercubeᵀᴹ Over a year ago

This will include the empty strings in SQL-Server.

lordkain Over a year ago

In oracle the empty string is translate to NULL so with the results form oracle and sql are the same with this statement

Ben Over a year ago

It returns an incorrect result in SQL Server

Collectives™ on Stack Overflow

Oracle empty string/NULL

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related