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