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

So, even though '' is null in Oracle, I still cannot see why column <> '' will not work. If column has a (non-empty) value, the condition will return true, which is what you want. What am I missing? Moreover, if column is empty, which means 'is null' for Oracle, the condition will return 'false' or 'unknown', the latter being the same as the former in this case. So, again, why can it not be used in Oracle? — Andriy M
– Andriy M, Commented Jun 24, 2011 at 8:40
@Andriy: I think the problem is that in Oracle, column <> '' will be evaluated as column <> NULL so 'some text' <> '' will be evaluated as 'some text' <> NULL = NULL (or Unknown as you call it). I guess one would like that to yield True and not Unknown. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jun 24, 2011 at 11:06
@ypercube: I see now. All the time I was thinking only about column and missed the '' as the right part of <>. Yes, it's all clear now, thank you! I mean, although you only think so, that's very much like what happens actually. — Andriy M
– Andriy M, Commented Jun 24, 2011 at 11:10

wp78de · Accepted Answer · 2018-12-27 00:31:57Z

40

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.

edited Dec 27, 2018 at 0:31

wp78de

19.1k7 gold badges49 silver badges78 bronze badges

answered Jun 24, 2011 at 5:43

Cheran Shunmugavel

8,4691 gold badge35 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary Myers · Accepted Answer · 2011-06-24 04:58:52Z

8

How about

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

answered Jun 24, 2011 at 4:58

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

2 Comments

Eduardo Over a year ago

It works, but I prefer the NULLIF(column, '') IS NOT NULL solution as the column is only once and it is shorter.

Andriy M Over a year ago

Basically, the NULLIF version, expanded. (+1)

DCookie · Accepted Answer · 2011-06-24 04:09:33Z

1

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.

edited Jun 24, 2011 at 4:09

answered Jun 23, 2011 at 17:36

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

Comments

ypercubeᵀᴹ · Accepted Answer · 2011-06-25 06:10:04Z

0

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 )

edited Jun 25, 2011 at 6:10

answered Jun 24, 2011 at 5:50

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

2 Comments

DCookie Over a year ago

Does SQL Server allow you to directly compare to null? I can't test it, just asking. If not, your first test will fail when column is NULL. The second test will fail for sure on Oracle when column is NULL, because NULL = NULL is false.

ypercubeᵀᴹ Over a year ago

@DCookie: Well, when column is NULL, we want it to fail, don't we?

Collectives™ on Stack Overflow

Compatible SQL to test for not null and not empty strings

4 Answers 4

Comments

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related