How to use nvl in sql

The NVL function replaces NULL values in SQL, and its syntax is NVL(expression, replacement_value). It handles multiple NULL values and nested usage by checking the first parameter expression and replacing it with the second parameter replacement_value if it is NULL. Note that if replacement_value is NULL, the NVL function will return NULL and does not work with date or time data types.

Usage of NVL function in SQL

The NVL function is used in SQL to replace NULL values. It takes two parameters:

• First parameter: the expression to check
• Second parameter: the replacement value if the expression is NULL

Syntax:

NVL(expression, replacement_value)

Usage example:

Replace the NULL "phone_number" field in the customer table with the default value "N/A":

SELECT customer_name, NVL(phone_number, 'N/A') AS phone_number FROM customers;

Handling multiple NULL values:

Use the NVL function to handle multiple NULL values. For example, the following query uses multiple NVL functions to replace NULL values in three fields:

SELECT customer_name, NVL(address, 'N/A') AS address, NVL(city, 'N/A') AS city, NVL(state, 'N/A') AS state FROM customers;

Nested NVL functions:

You can use nested NVL functions to handle complex situations. For example, the following query uses a nested NVL function to return the customer's best contact method:

SELECT customer_name, NVL(email, NVL(phone_number, 'N/A')) AS best_contact FROM customers;

Note:

• If replacement_value is also NULL, then the NVL function will return NULL.
• NVL functions are not suitable for date or time type data.

The above is the detailed content of How to use nvl in sql. For more information, please follow other related articles on the PHP Chinese website!