General functions of NVL, NVL2, NULLIF, and COALESCE in oracle

Source: Internet
Author: User
Tags mathematical functions

The string processing, date functions, mathematical functions, and conversion functions previously introduced in Oracle are also common functions. Mainly include NVL, NVL2, NULLIF, and COALESCE. These functions can be used in various types.

The following describes the usage of several functions.

Before introducing this, you must understand what is null in oracle?

1. NVL Function

The NVL function format is as follows: NVL (expr1, expr2)

Meaning: if the first oracle parameter is null, the value of the second parameter is displayed. If the value of the first parameter is not empty, the original value of the first parameter is displayed.

For example:

SQL> select ename, NVL (comm,-1) from emp;

Ename nvl (COMM,-1)

-----------

SMITH-1

ALLEN 300

WARD 500

JONES-1

MARTIN 1400

BLAKE-1

FORD-1

MILLER-1

The original values of-1 are all null values.

2 NVL2 Functions

The format of the NVL2 function is as follows: NVL2 (expr1, expr2, expr3)

Meaning: if the first parameter of the function is null, the value of the second parameter is displayed. If the value of the first parameter is not null, the value of the third parameter is displayed.

SQL> select ename, NVL2 (comm,-1, 1) from emp;

ENAME NVL2 (COMM,-1, 1)

------------

SMITH 1

ALLEN-1

WARD-1

JONES 1

MARTIN-1

BLAKE 1

CLARK 1

SCOTT 1

In the above example. If the result is 1, it is not null, but the original value of-1 is null.

3. NULLIF Function

The NULLIF (exp1, expr2) function returns NULL if exp1 and exp2 are equal. Otherwise, the first value is returned.

The following is an example. The HR schema in oracle is used. If HR is locked, enable

The role here is to show the original work of those who have changed their jobs.

SQL> SELECT e. last_name, e. job_id, j. job_id, NULLIF (e. job_id, j. job_id) "Old Job ID"

FROM employees e, job_history j

WHERE e. employee_id = j. employee_id

Order by last_name;

LAST_NAME JOB_ID Old Job ID

--------------------------------------

De Haan AD_VP IT_PROG AD_VP

Hartstein MK_MAN MK_REP MK_MAN

Kaufling ST_MAN ST_CLERK ST_MAN

Kochhar AD_VP AC_MGR AD_VP

Kochhar AD_VP AC_ACCOUNT AD_VP

Raphaely PU_MAN ST_CLERK PU_MAN

Taylor SA_REP SA_MAN SA_REP

Taylor SA_REP

Whalen AD_ASST AC_ACCOUNT AD_ASST

Whalen AD_ASST

We can see all the employees. If job_id and job_histroy.job_id are equal, NULL is output in the result, otherwise the result is displayed as employee. Job_id

4. Coalesce Function

The role of the Coalese function is that the NVL function is somewhat similar. Its advantage is that there are more options.

The format is as follows:

Coalesce (expr1, expr2, expr3 ..... Exprn)

Coalesce is used to process these parameters. If the first parameter is null, check whether the second parameter is null. Otherwise, the first parameter is displayed. If the second parameter is null, check whether the third parameter is null, otherwise, the second parameter is displayed, and so on.

This function is actually used cyclically by NVL. We will not give an example here.

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.