Convert null values to actual values

Convert null values to actual values
Create a test table:

Create or replace view v asselect null as c1, null as c2, '1' AS c3, null as c4, '2' AS c5 FROM dualUNION allselect null as c1, null as c2, null as c3, '3' AS c4, '2' AS c5 FROM dual; SELECT * FROM v;

If you want to query the value of column c3, When Column c3 is empty, it is displayed as 0. There are two methods:

-- Use the nvl function select nvl (c3, '0') c3 FROM v; -- use the coalesce function select coalesce (c3, '0') c3 FROM v;

The query result is as follows: if you want to query the first non-empty value from column c1 to column c5, the following two methods are available:

-- Method 1 select nvl (c1, c2), c3), c4), c5) AS c from v; -- method 2 select coalesce (c1, c2, c3, c4, c5) AS c FROM v;

The query result is as follows: as shown in the following figure, coalesce supports multiple parameters, which can easily return the first non-null value.