The difference between several functions of Oracle NVL,NVL2,COALESCE

Source: Internet
Author: User

1.NVL (EXP1,EXP2)

NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null and then NVL returns expr2. If expr1 is isn't null, then NVL returnsexpr1.

The function is to handle null values in an expression: Assuming that the expression EXP1 is null, the function returns the value of the expression Exp2,

Assuming that the expression EXP1 is not a null value, the function returns the value of the expression Exp1.

2.NVL2 (EXP1,EXP2,EXP3)

NVL2lets determine the value returned by a query based on whether a specified expression was null or not NULL. Ifexpr1is isn't null, thenNVL2returnsexpr2. Ifexpr1is null and thenNVL2returnsexpr3.

This function is determined to return an expression when EXP1 is null and non-null values:

If EXP1 is a null value, the EXP3 is returned

assume that EXP1 is non-null and returns EXP2.

3.coalesce (EXP1,EXP2, ...)

COALESCEreturns the first non-null expr In the expression list. You must specify at least and expressions. If all occurrences expr of evaluate to NULL, then the function returns NULL.

The function returns the first non-null value in the full expression in parentheses, assuming that it is null, and returns a null value with a minimum of two expressions.

Example:


This article from "Angel Wings" blog, declined reprint!

The difference between several functions of Oracle NVL,NVL2,COALESCE

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.