Oracle 字符null 处理,有点儿晕了

---

1.Oracle 中对null的描述

null
Absence of a value in a column of a row. Nulls indicate missing, unknown, or inapplicable data. 

NULL 是数据库中特有的数据类型，当一条记录的某个列为 NULL ，则表示这个列的值是未知的、是不确定的既然是未知的，就有无数种的可能性。因此， NULL 并不是一个确定的值

2.null的判断

-- 判断一个列为空
SELECT * FROM CUSTOMEREN C WHERE C.MODIFYDT IS NULL;

-- 判断一个列非空
SELECT * FROM CUSTOMEREN C WHERE C.MODIFYDT IS NOT NULL;

3.null 的比较

1. NULL=NULL么？

SELECT CASE
         WHEN NULL = NULL THEN
          'NULL is equal to NULL'
         ELSE
          'NULL is not equal to NULL'
       END "NULL=NULL?"
  FROM DUAL;
--结果NULL is not equal to NULL

2. 空串=NULL么？

SELECT CASE
         WHEN '' = NULL THEN
          '空串 is equal to NULL'
         ELSE
          '空串 is not equal to NULL'
       END "空串=NULL?"
  FROM DUAL;
--结果空串 is not equal to NULL

 3.空格=NULL么？

SELECT CASE
         WHEN ' ' = NULL THEN
          '空格 is equal to NULL'
         ELSE
          '空格 is not equal to NULL'
       END "空格=NULL?"
  FROM DUAL;
--结果空格 is not equal to NULL

4.空串'' 是NULL么 

SELECT CASE
         WHEN '' IS NULL THEN
          ''''' is NULL'
         ELSE
          ''''' is not NULL'
       END "'' is NULL?"
  FROM DUAL;
--结果 '' is NULL

5.空串=空串么

SELECT CASE
         WHEN '' = '' THEN
          ''''' is equal to '''''
         ELSE
          ''''' is not equal to '''''
       END "''''=''''?"
  FROM DUAL;
--结果'' is not equal to ''

4.null替换

--nvl函数将null替换成一个值

 select 1 from dual where nvl(null,0)=nvl(null,0);

---

5.null排序

--方法 1  将null替换成一个值 ,是用nvl 函数

select 1 from dual where nvl(null,0)=nvl(null,0);


--方法2 是用NULLS LAST，NULLS FIRST处理　

SELECT c.modifydt FROM producten c ORDER BY c.modifydt NULLS LAST;

SELECT c.modifydt FROM producten c ORDER BY c.modifydt NULLS FIRST;

上一篇：ORA-06512 数字或值错误字符串缓冲区太小