Oracle database

COALESCE Function of oracle database

Written by meda

COALESCE Function

COALESCE Function is an oracle database function and has many parameters. The function returns the first parameter from the list that has nonnull value.

The syntax for COALESCE Function is :

COALESCE(expr1, expr2,…,exprn)

The value of the ‘expr1‘ parameter above is returned if it is not null, unless the value of the ‘expr2‘ parameter is returned if it is not null, and so on.

Here below is an  example  using SQL*PLUS command-line tool.

D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Feb 26 10:45:09 2022

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> conn sys/orcl@orcl as sysdba
Connected.

SQL> select coalesce(null, null, null, 'Mereba') as value from dual;

VALUE
------
Mereba

SQL> select coalesce(null, 'Technology, null) as value from dual;

VALUE
----------
Technology

SQL> select coalesce(null, 'is', null) as value from dual;

VA
--
is

SQL> select coalesce(substr('merebaThe best',7),'programming','IT') from dual;

COALESCE
--------
The best

Here also we have a table named tbl_customer and has swift_code column of null values. Let’s run the following query and see the results.

D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 7 08:47:26 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

SQL> CONN mereba/mereba@orcl
Connected.
SQL> col cust_no format a30;
SQL> col customer_type format a10;
SQL> col cust_name format a23;
SQL> col country  format a15;
SQL> col swift_code format a14;
SQL> SELECT cust_no, COALESCE(swift_code,'No_swift_code')swift_code,cust_name,country FROM TBL_CUSTOMER;

   CUST_NO SWIFT_CODE     CUST_NAME               COUNTRY
---------- -------------- ----------------------- ---------------
########## No_swift_code  Messi John Dave         USA
########## No_swift_code  Mereba Technology       UK
########## No_swift_code  Swiss Bank              Switzerland

SQL>

As you can see in the query above ,the function returns ‘No_swift_code’ value because the column does not have value in the database.