How to replace null value in oracle

In Oracle, you can use the nvl() function to replace null values. This function is used to return a non-null value from two expressions. The syntax is "nvl (field value, replacement value)"; When the result of the field value is a null value, the function will return the replacement value. If the result of the field value is not a null value, the function will return the original field value.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to replace null values ​​in oracle

nvl If a field is empty and you want it to be displayed as 0 or other values, you can use the nvl function, such as nvl (field name, replacement value )

Return a non-null value from two expressions.

Syntax

NVL(eExpression1, eExpression2)

If eExpression1 evaluates to a null value, NVL( ) returns eExpression2. If eExpression1 evaluates to something other than null, eExpression1 is returned. eExpression1 and eExpression2 can be of any data type. If the results of both eExpression1 and eExpression2 are null values, NVL( ) returns .NULL.

Return value type

Character type, date type, date and time type, numeric type, currency type, logical type or null value

Does not support null value or null value When it is not important, you can use NVL( ) to remove null values ​​from calculations or operations.

select nvl(a.name,'空得') as name from student a join school b on a.ID=b.ID

Note: The types of the two parameters must match

  SELECT           T.D_FDATE,
                    T.VC_ZHCODE,
                         NVL(SUM(T.F_FZQSZ), 0) f_price_b,
                         NVL(SUM(T.F_FZQCB), 0) f_cost_b,
                         NVL(SUM(T.F_FGZ_ZZ), 0) f_gz_b,
                         NVL(SUM(T.F_FYZQSZ), 0) f_price_Y,
                        NVL(SUM(T.F_FYZQCB), 0) f_cost_Y,
                        NVL(SUM(T.F_FYGZ_ZZ), 0) f_gz_Y,
                        T.VC_SOURCE,
                        SYSDATE d_updatetime
                   FROM GZ_FUND_GZB T

For example, such a judgment is very important, because you don’t know which line is is not If it is null, we don’t know whether we need to perform calculations on this cell next. Therefore, we cannot fill in null for the column, so just give it a 0, which is convenient for viewing and calculation.

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of How to replace null value in oracle. For more information, please follow other related articles on the PHP Chinese website!