What is the usage of nvl in oracle

The nvl function in Oracle is used to handle null values. It returns the corresponding alternative value by determining whether the expression is null. Common usage: 1. Used to replace null value; 2. Used to replace null value with numerical value; 3. Used to replace null value with date; 4. Used to check null value; 5. Used for conditional judgment.

#The operating environment of this article: Windows 10 system, Oracle version 19c, dell g3 computer.

The oracle nvl function is a function used to handle null values ​​and is often used in queries and calculations. Its syntax is as follows:

NVL (expr1, expr2)

Among them, expr1 is the expression to be checked for null, and expr2 is the substitute value returned when expr1 is null.

The nvl function is used as follows:

1. Used to replace the null value

SELECT NVL(column_name, 'N/A') AS new_column_name
FROM table_name;

The above query replaces the null value in the column_name column with 'N/A'.

2. Used to replace null values ​​with numerical values

SELECT NVL(column_name, 0) AS new_column_name
FROM table_name;

The above query replaces the null value in the column_name column with 0.

3. Used to replace null values ​​in date

SELECT NVL(column_name, TO_DATE('01-Jan-1900', 'dd-Mon-yyyy')) AS new_column_name
FROM table_name;

The above query replaces the null value in the column_name column with '01-Jan-1900'.

4. Used to check null values

SELECT column_name
FROM table_name
WHERE NVL(column_name, 'N/A') = 'N/A';

The above query will return records with a null value in the column_name column.

5. Used for conditional judgment

SELECT column_name
FROM table_name
WHERE NVL(column_name, 0) > 100;

The above query will return records with a value greater than 100 in the column_name column. If the value is null, the default is 0.

Summary:

The nvl function is a function used in Oracle to handle null values. It returns the corresponding alternative value by determining whether the expression is null. It can be used to replace null values, replace null values ​​with values, replace null values ​​with dates, check null values ​​and conditional judgments, etc. For queries and calculations that need to handle null values, the nvl function is a very practical tool.

The above is the detailed content of What is the usage of nvl in oracle. For more information, please follow other related articles on the PHP Chinese website!