CODEWITHSUNDEEP
sqlsql-serveroracle-database
Eduardo
Eduardo

Reputation: 2341

Compatible SQL to test for not null and not empty strings

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

Upvotes: 22

Views: 57939

Answers (4)

Cheran Shunmugavel
Cheran Shunmugavel

Reputation: 8459

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.

Upvotes: 40

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115560

A try to shorten @DCookie's answer. I like his ( '' = '' ) test.

CASE WHEN ( '' = '' ) THEN ( column <> '' )
                      ELSE ( column = column )
END

Sadly, the above will not work. The next works in SQL-Server. I can't test in Oracle now:

CASE WHEN  '' = ''  THEN CASE WHEN column <> ''    THEN 1 ELSE NULL END 
                    ELSE CASE WHEN column = column THEN 1 ELSE NULL END 
END

which can be written also as:

    ( '' = ''    AND column <> '' )
 OR ( '' IS NULL AND column = column )

Upvotes: 0

Gary Myers
Gary Myers

Reputation: 35401

How about

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

Upvotes: 8

DCookie
DCookie

Reputation: 43533

I think the key here is to differentiate between the case when the empty string is equivalent to NULL and when it isn't:

WHERE CASE WHEN '' = '' THEN -- e.g., SQL Server this is true
              CASE WHEN col <> '' AND col IS NOT NULL THEN 'Y'
                   ELSE 'N'
              END
           WHEN COALESCE(col,NULL) IS NOT NULL THEN 'Y' -- Not SS, e.g., Oracle
           ELSE 'N'
      END = 'Y';

If the first case is true then empty string is not the same as null, and we have to test for both string being not null and string not being the empty string. Otherwise, our task is easier because empty string and null evaluate the same.

Upvotes: 1

Related Questions