Compatible SQL to test for not null and not empty strings

Question

I want to have compatible SQL for both Oracle database and Microsoft SQL server.

I want a compatible SQL expression that will return true for not null and not empty strings.

If I use:

column <> '' 

it will work on Microsoft SQL server but not on Oracle database (as '' is null for Oracle)

If I use:

len(column) > 0 

it will work on Microsoft SQL server but not on Oracle database (since it uses length() )

Answer 1

NULLIF is available on both Oracle (doc) and SQL Server (doc). This expression should work:

NULLIF(column, '') IS NOT NULL 

In both servers, if column is NULL, then the output of NULLIF will just pass the NULL value through. On SQL Server, '' = '', so the output of NULLIF will be NULL. On Oracle, '' is already NULL, so it gets passed through.

This is my test on SQL Server 2008 R2 Express:

WITH SampleData AS     (SELECT 1 AS col1, NULL AS col2      UNION ALL      SELECT 2, ''      UNION ALL      SELECT 3, 'hello') SELECT *   FROM SampleData  WHERE NULLIF(col2, '') IS NOT NULL; 

And this is my test case on Oracle 10g XE:

WITH SampleData AS     (SELECT 1 AS col1, NULL AS col2 FROM DUAL      UNION ALL      SELECT 2, '' FROM DUAL      UNION ALL      SELECT 3, 'hello' FROM DUAL) SELECT *   FROM SampleData  WHERE NULLIF(col2, '') IS NOT NULL; 

Both return 3 as expected.

Answer 2

How about

CASE WHEN column = '' THEN NULL ELSE column END IS NOT NULL