Coalesce and coalesce

Coalesce and coalesce

There is such a demand. There are several contacts in the community. If there is a power outage, first notify contact 1. If not, then notify contact 2; otherwise, notify contact 3.

Create table test (
Id number,
Name varchar2 (20 ),
Contact_phone1 varchar2 (20 ),
Contact_phone2 varchar2 (20 ),
Contact_phone3 varchar2 (20 ),
Contact_phone4 varchar2 (20)
);
Insert into test values (1, 'cell 1', '','', '123 ','');
Insert into test values (1, 'cell 2', '', '123 ');
Commit;

SQL> select * from test;
Id name CONTACT_PHONE1 CONTACT_PHONE2 CONTACT_PHONE3 CONTACT_PHONE4
---------------------------------------------------------------------
1 Residential Area 1 13485958522
1 Residential Area 2 13488958536

Without this function, you can first check the data and then use the java code for processing. Coalesce ranges from left to right. In case of the first non-null value, this non-null value is returned, which is determined by multiple layers.

SQL> select id, name, coalesce (contact_phone1,
Contact_phone2,
Contact_phone3,
Contact_phone4) as contact_phone
From test;
Id name CONTACT_PHONE
--------------------------------------------------
1 Residential Area 1 13485958522
1 Residential Area 2 13488958536