SQL 带有空字符串的 Oracle NVL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26274750/
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
Oracle NVL with empty string
提问by kmkaplan
I have this table where NULL is the NULLvalue, not the string NULL:
我有这个表，其中 NULL 是NULL值，而不是字符串 NULL：
MYCOL
--------
NULL
example
Why does this query notreturn the NULLrow?
为什么这个查询不返回NULL行？
select * from example_so where nvl(mycol, '') = '';
回答by Maheswaran Ravisankar
''is again NULLin Oracle, because Oracle doesn't support empty Strings just like Other High Level languages or DBMS..
''再次出现NULL在 Oracle 中，因为 Oracle 不像其他高级语言或 DBMS 那样支持空字符串。
You need to look for NULL/empty string using IS NULLor IS NOT NULL
您需要使用IS NULL或查找NULL/空字符串IS NOT NULL
No other relational operator work against NULL, though it is syntactically valid. SQLFiddle Demo
没有其他关系运算符对 起作用NULL，尽管它在语法上是有效的。SQLFiddle 演示
It has to be,
它一定要是，
select * from example_so where mycol IS NULL
EDIT:As per Docs
编辑：根据文档
Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.
Oracle 数据库当前将长度为零的字符值视为空值。但是，这在未来版本中可能不再适用，Oracle 建议您不要将空字符串视为空字符串。
回答by Lalit Kumar B
Because NULL = NULLis simply unknown. A third state perhaps? It is neither TRUEnor FALSE.
因为NULL = NULL根本不为人知。也许是第三种状态？它既不是TRUE也不是FALSE。
Oracle considers an EMPTY STRING as NULL.
Oracle 将 EMPTY STRING 视为 NULL。
nvl(mycol, '')makes no real sense, as you are making the NULL back to NULL and comparing it again with NULL.
nvl(mycol, '')没有任何意义，因为您正在将 NULL 变回 NULL 并再次与 NULL 进行比较。
SQL> WITH DATA AS(
2 SELECT 1 val, 'NULL' str FROM dual UNION ALL
3 SELECT 2, NULL str FROM dual UNION ALL
4 SELECT 3, '' str FROM dual UNION ALL
5 SELECT 4, 'some value' str FROM dual)
6 SELECT val, NVL(str, 'THIS IS NULL') FROM data WHERE str IS NULL
7 /
VAL NVL(STR,'THI
---------- ------------
2 THIS IS NULL
3 THIS IS NULL
SQL>
回答by user3380585
select * from example_so where nvl(mycol, '') = '';
select * from example_so where nvl(mycol, '') = '';
nvl(mycol, '') will be resulted as NULLand when you compared NULL with empty string it cant be compared
nvl(mycol, '') 将结果为 NULL，当您将 NULL 与空字符串进行比较时，无法进行比较
create table t(id varchar2(2));
insert into t values (nvl(null,'')); <------ this will insert NULL
insert into t values (nvl(null,'et'));
