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

Answers


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


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.


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

Need Your Help

How can I append a dynamic text with a static text in html?

asp.net html login append

I'm working on a login page, I want the user to see his name beside Welcome"X" when he login..

How to get Google User ID using the GMail address in Android?

android google-app-engine gmail

How to get Google User ID something which looks like "1242343543557656",using the GMail address in Android?