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


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

Need Your Help

Android / Java convert String date to long type

java android date calendar android-contentprovider

I need to convert a string in the format "dd/mm/yyyy", to a long type. In order to pass the value to the calendarProvider in android.

Can dimensions be skipped in queries against R/R*/X-Trees?

geospatial spatial multidimensional-array spatial-query r-tree

Let's say we have a 4 dimensional tree with dimensions longitude, latitude, altitude and for illustration purposes, price.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.