How to Use COALESCE with Different Data Types

The Oracle COALESCE function is a handy function, but it can be hard to make it work if you want to consider fields with different data types. I'll show you how you can do this in this tip.

What Is Oracle COALESCE?

The Oracle COALESCE function allows you to check multiple values and return the first non-NULL value.

The syntax of this function is:

COALESCE( expr1, expr2, [expr...] )

It checks each of the expressions in order, and returns the first one it finds that is not NULL.

However, all of the expressions need to be the same data type.

Using Different Data Types

What if you wanted to use the COALESCE function on columns or values that had different data types?

Let's see an example of this.

Say you had a table that looked like this:

CREATE TABLE customers (
first_name varchar2(100),
last_name varchar2(100),
country varchar2(20),
full_address CLOB,
employees number,
start_date date
);

If we ran a SELECT on this table (which I had populated with some data), it may look like this:

FIRST_NAME	LAST_NAME	COUNTRY	FULL_ADDRESS	EMPLOYEES	START_DATE
Adam	Jones	USA	10 Main Street	20	12/JAN/15
Brad	Smith	USA	(null)	45	04/SEP/15
Carrie	Johnson	USA	14 Long Avenue	6	(null)
(null)	Lane	USA	1 Main Road	2	(null)

What if I wanted to use a COALESCE function and replace the first_name with the full_address?

SELECT first_name, full_address, employees, start_date, 
COALESCE(first_name, full_address) AS coal
FROM customers;

This will give me an error, because the two fields have different data types.

ORA-00932: inconsistent datatypes: expected CHAR got CLOB

How can I fix this?

How to Use Coalesce With Different Data Types

The way to use the COALESCE function with different data types is to convert all parameters to be the same data type as the first parameter.

If we did want to use a CLOB value to substitute for a NULL VARCHAR2 value, then we could use the TO_CHAR function on the CLOB value.

SELECT first_name, full_address, employees, start_date,
COALESCE(first_name, TO_CHAR(full_address)) AS coal
FROM customers;

FIRST_NAME	FULL_ADDRESS	EMPLOYEES	START_DATE	COAL
Adam	10 Main Street	20	12/JAN/15	Adam
Brad	(null)	45	04/SEP/15	Brad
Carrie	14 Long Avenue	6	(null)	Carrie
(null)	1 Main Road	2	(null)	(null)

This has worked. It's because the COALESCE function needs to use the data type of the first parameter.

What if you wanted to use the employees field, or any other numeric field?

You could convert that as well.

SELECT first_name, full_address, employees, start_date,
COALESCE(first_name, TO_CHAR(employees)) AS coal
FROM customers;

What if you wanted to use a number as the first parameter, and the other parameters are VARCHAR2 values?

You could try to convert the first parameter to match, because you can't convert VARCHAR2 to NUMBER.

SELECT first_name, full_address, employees, start_date,
COALESCE(TO_CHAR(employees), first_name) AS coal
FROM customers;

What if you were using a date field?

SELECT first_name, full_address, employees, start_date,
COALESCE(start_date, first_name) AS coal
FROM customers;

You'll get a similar error when running this query.

ORA-00932: inconsistent datatypes: expected DATE got CHAR

How can we resolve this?

Let's try converting the start_date to a string using TO_CHAR.

SELECT first_name, full_address, employees, start_date,
COALESCE(TO_CHAR(start_date), first_name) AS coal
FROM customers;

FIRST_NAME	FULL_ADDRESS	EMPLOYEES	START_DATE	COAL
Adam	10 Main Street	20	12/JAN/15	12/JAN/15
Brad	(null)	45	04/SEP/15	04/SEP/15
Carrie	14 Long Avenue	6	(null)	Carrie
(null)	1 Main Road	2	(null)	(null)

Yes, this seems to work.

So, converting data types of the parameters to match the first parameter seems to work for many different data types. This is something to remember if you're trying to use COALESCE with different data types.