用于测试非空和非空字符串的兼容 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6457710/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Compatible SQL to test for not null and not empty strings
提问by Eduardo
I want to have compatible SQL for both Oracle database and Microsoft SQL server.
我想为 Oracle 数据库和 Microsoft SQL 服务器提供兼容的 SQL。
I want a compatible SQL expression that will return true for not null and not empty strings.
我想要一个兼容的 SQL 表达式，该表达式将为非空字符串和非空字符串返回 true。
If I use:
如果我使用：
column <> ''
it will work on Microsoft SQL server but not on Oracle database (as '' is null for Oracle)
它适用于 Microsoft SQL 服务器，但不适用于 Oracle 数据库（因为 Oracle 的 '' 为空）
If I use:
如果我使用：
len(column) > 0
it will work on Microsoft SQL server but not on Oracle database (since it uses length() )
它适用于 Microsoft SQL 服务器，但不适用于 Oracle 数据库（因为它使用 length() ）
回答by Cheran Shunmugavel
NULLIFis available on both Oracle (doc) and SQL Server (doc). This expression should work:
NULLIF可在 Oracle ( doc) 和 SQL Server ( doc) 上使用。这个表达式应该有效：
NULLIF(column, '') IS NOT NULL
In both servers, if columnis NULL, then the output of NULLIFwill just pass the NULLvalue through. On SQL Server, '' = '', so the output of NULLIFwill be NULL. On Oracle, ''is already NULL, so it gets passed through.
在两台服务器中，如果column是NULL，则 的输出NULLIF只会传递NULL值。在 SQL Server 上，'' = ''，因此 的输出NULLIF将为NULL. 在 Oracle 上，''is already NULL，因此它会通过。
This is my test on SQL Server 2008 R2 Express:
这是我在 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:
这是我在 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 3as expected.
两者都3按预期返回。
回答by Gary Myers
How about
怎么样
CASE WHEN column = '' THEN NULL ELSE column END IS NOT NULL
回答by DCookie
I think the key here is to differentiate between the case when the empty string is equivalent to NULL and when it isn't:
我认为这里的关键是区分空字符串等同于 NULL 和不等同于 NULL 的情况：
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.
如果第一种情况为真，则空字符串与空字符串不同，我们必须测试字符串是否为空字符串和字符串是否为空字符串。否则，我们的任务会更容易，因为空字符串和 null 的计算结果相同。
回答by ypercube??
A try to shorten @DCookie's answer. I like his ( '' = '' )test.
尝试缩短@DCookie 的答案。我喜欢他的( '' = '' )考试。
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:
可悲的是，上述方法不起作用。下一个在 SQL-Server 中工作。我现在无法在 Oracle 中进行测试：
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 )
