CODEWITHSUNDEEP
sqlsql-serveroracle-database
Marko
Marko

Reputation: 476

Oracle empty string/NULL

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

Upvotes: 5

Views: 1849

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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).

Upvotes: 7

lordkain
lordkain

Reputation: 3109

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 <> '')

Upvotes: -2

Ben
Ben

Reputation: 52863

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.

Upvotes: 2

Related Questions