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