Oracle SQL Query to Return Second Column if First Column is Null
There are multiple ways to return a column that is not null out of 2, 3 or more columns. The SQL statements that can be used for this purpose are NVL, NVL2, CASE, and COALESCE
W3Courses was launched in 2003 and has been providing information on technology, business, jobs, and health. W3Courses is continuously adding articles to deliver quality information.
SQL, Oracle
Example: In the following table called Customers, return only one Phone Number with priority to CellPhone first then DayPhone. Return Cellphone is CellPhone is not Null. If CellPhone is Null, return DayPhone.
| CustomerName | CellPhone | DayPhone |
| Joe Smith | 916-220-4040 | |
| Kevin Brada | 559-230-5454 | |
| Homer Lopez | 559-646-3232 |
NVL
SELECT CustomerName, NVL(CellPhone, DayPhone) as PhoneNumber FROM Customers;
NVL2
SELECT CustomerName, NVL2(CellPhone, CellPhone, DayPhone) as PhoneNumber FROM Customers;
CASE
SELECT CustomerName, CASE WHEN CellPhone IS NULL THEN DayPhone ELSE CellPhone END as PhoneNumber FROM
Customers;
COALESCE
SELECT CustomerName, COALESCE(CellPhone, DayPhone) as PhoneNumber FROM Customers;
All of the above Queries will produce the following result set
| CustomerName | PhoneNumber |
| Joe Smith | 916-220-4040 |
| Kevin Brada | 559-230-5454 |
| Homer Lopez | 559-646-3232 |
Author
W3Courses was launched in 2003 and has been providing information on technology, business, jobs, and health. W3Courses is continuously adding articles to deliver quality information.
Comments
Tags
SQL, Oracle
Become a Contributor
- 852 reads