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

Answer 1

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