Description
The Oracle/PLSQL NVL function lets you substitute a value when a null value is encountered.
NVL函数是当出现空值时替换一个值
Syntax
NVL( string1, replace_with )
String1 | The string to test for a null value. |
replace_with | The value returned if string1 is null. |
Example
For example:
SELECT NVL(supplier_city, 'n/a') | The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value. |
SELECT supplier_id, | This SQL statement would return the supplier_name field if the supplier_desc contained a null value. Otherwise, it would return the supplier_desc. |
SELECT NVL(commission, 0) | This SQL statement would return 0 if the commission field contained a null value. Otherwise, it would return the commission field. |
Frequently Asked Questions
Question: | Answer: |
I tried to use the NVL function through VB to access Oracle DB. To be precise, SELECT NVL(Distinct (emp_name),'AAA'),................ I got an oracle error when I use distinct clause with NVL, but when I remove distinct it works fine. | It is possible to the use the DISTINCT clause with the NVL function. However, the DISTINCT must come before the use of the NVL function. For example: SELECT distinct NVL(emp_name, 'AAA') |
Is it possible to use the NVL function with more than one column with the same function call? To be clear, if i need to apply this NVL function to more than one column like this: NVL(column1;column2 ...... , here is the default value for all ) | Answer: You will need to make separate NVL function calls for each column. For example: SELECT NVL(table_name, 'not found'), NVL(owner, 'not found') |
Examine the TRAINING table as given below: Name Null? Type TRAINING_ID NOT NULL NUMBER(5) Which two SQL would execute successfully? (Choose two)
| A, D. Use NVL function to provide an alternate value to a column when NULL. |
使用DISTINCT 的方法COUNT函数和NVL函数的区别:
NVL | SELECT DISTINCT NVL(emp_name, 'AAA') |
COUNT | SELECT COUNT(DISTINCT department) AS "Unique departments" |
From <https://www.techonthenet.com/oracle/functions/nvl.php>