處理空值與非數位函數詳解-雲原生資料庫 PolarDB-阿里雲

本文介紹了NULL值相關的函數的文法、參數以及樣本等內容。

NANVL

描述

該函數用於判斷n1是否為NaN（非數字），並返回結果。

如果n1為NaN，則函數返回n2。
如果n1是數字，則函數返回n1。

文法

NANVL(n1, n2)

參數

參數	說明
n1	僅支援`BINARY_FLOAT`和`BINARY_DOUBLE`類型的浮點數。
n2	僅支援`BINARY_FLOAT`和`BINARY_DOUBLE`類型的浮點數。

傳回型別

返回BINARY_FLOAT或者BINARY_DOUBLE類型。

樣本

CREATE TABLE float_point_demo (bf BINARY_FLOAT,bd BINARY_DOUBLE);
INSERT INTO float_point_demo VALUES (123.456,'NaN');
INSERT INTO float_point_demo VALUES ('NAN',123.456);

SELECT * FROM float_point_demo;
   bf    |   bd    
---------+---------
 123.456 |     NaN
     NaN | 123.456

SELECT NANVL(bf,0) "nanvl_1", NANVL(bd,0) "nanvl_2" FROM float_point_demo;
 nanvl_1 | nanvl_2 
---------+---------
 123.456 |       0
       0 | 123.456

NVL2

描述

該函數用於根據指定的運算式是否為NULL來確定查詢返回的值。

如果expr1不為NULL，則NVL2返回expr2。
如果expr1為NULL，則NVL2返回expr3。

文法

NVL2(expr1, expr2, expr3)

參數

參數	說明
expr1	可以為任意資料類型。
expr2	可以為除`LONG`以外的任意資料類型。
expr3	可以為除`LONG`以外的任意資料類型。

傳回型別

如果expr2和expr3的資料類型相同，則返回該相同的資料類型。
如果expr2和expr3的資料類型不同：
- 如果expr2是字元資料，則資料庫在比較它們之前將expr3轉換為expr2的資料類型，除非expr3是null常量。在這種情況下，不需要進行資料類型轉換。在expr2的字元集中返回VARCHAR2。
- 如果expr2是數字，則資料庫確定哪個參數具有最高的數字優先順序，將另一個參數隱式轉換為該資料類型，並返回該資料類型。

樣本

SELECT nvl2('A'::text, 'B', 'C') FROM DUAL;
 nvl2 
------
 B

SELECT nvl2(NULL, 2, 3) FROM DUAL;
 nvl2 
------
    3

NVL

描述

該函數用於將查詢結果中的NULL以空白形式返回來替換為字串。

如果expr1為NULL，則NVL返回expr2。
如果expr1不為NULL，則NVL返回expr1。

文法

NVL(expr1, expr2)

參數

參數	說明
expr1	可以為任意資料類型。
expr2	可以為任意資料類型。

傳回型別

參數expr1和expr2可以為任何資料類型。

如果它們的資料類型不同，那麼資料庫會隱式地將其中一種轉換為另一種。如果不能隱式轉換，則資料庫將返回一個錯誤。

隱式轉換的實現方式如下：

如果expr1是字元資料，則資料庫在比較它們之前將expr2轉換為expr1的資料類型，並在expr1的字元集中返回VARCHAR2類型資料。

如果expr1是數字類型，則確定哪個參數具有最高的數字優先順序，將另一個參數隱式轉換為該資料類型，並返回該資料類型。

樣本

SELECT nvl(NULL::text, 'B') FROM DUAL;
 nvl 
-----
 B

NULLIF

描述

NULLIF比較expr1和expr2。

文法

NULLIF(expr1, expr2)

參數

參數	說明
expr1	可以為任意資料類型。
expr2	可以為任意資料類型。

傳回型別

如果expr1和expr2相等，則函數返回NULL。
如果expr1和expr2不相等，則函數返回expr1。

樣本

SELECT nullif('a','') FROM DUAL;
 nullif 
--------
 a

select nullif('','') FROM DAUL;
 nullif 
--------

LNNVL

描述

該函數用於在條件的一個或兩個運算元可能為空白時，提供一種評分準則的簡明方法。

說明

LNNVL可以用於任何可能出現標量運算式的地方，即使在IS[NOT]NULL、AND或OR條件無效但需要考慮潛在NULL的情況下也是如此。資料庫有時會以這種方式在內部使用LNNVL函數，將NOT IN條件重寫為NOT EXISTS條件。在這種情況下，EXPLAIN PLAN的輸出會在計劃表輸出中顯示此操作。條件可以計算任何標量值，但不能是包含AND、OR或BETWEEN的複合條件。

文法

LNNVL(condition)

參數

參數	說明
condition	輸入的條件。

傳回型別

該函數只能在查詢的WHERE子句中使用，它將條件作為參數。

如果條件為FALSE或UNKNOWN，則返回TRUE。
如果條件為TRUE，則返回FALSE。

樣本

create table lnnvltbl(name varchar(10), id int);
insert into lnnvltbl values(null, 1); 
insert into lnnvltbl values('', 2);
insert into lnnvltbl values ('null',3);
insert into lnnvltbl values('s', 4);
insert into lnnvltbl values ('ss',5);
select * from lnnvltbl where lnnvl(name is not null) order by id;
 name | id 
------+----
      |  1
      |  2

COALESCE

描述

該函數返回運算式列表中的第一個非NULL運算式。至少有一個運算式不能為NULL。如果所有出現的expr都計算為NULL，則函數返回NULL。

資料庫使用短路評估。也就是說，資料庫評估每個expr值並確定它是否為NULL，而不是在確定其中任何一個是否為NULL之前評估所有expr值。

文法

COALESCE(expr [, expr]...)

參數

參數	說明
expr	可以是數字資料類型或者是字元資料類型的值或運算式。

傳回型別

如果所有出現的expr都是數字資料類型或任何可以隱式轉換為數字資料類型的非數字資料類型，則資料庫將確定具有最高數字優先順序的參數，隱式將其餘參數轉換為該資料類型，並返回該資料類型。

樣本

SELECT coalesce(null,3.934,1) FROM DUAL;
 coalesce 
----------
    3.934

select coalesce(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,1) FROM DUAL;
 coalesce 
----------
        1