Concatenating Strings whilst Catering for Nulls

Introduction

In this article, we will explore a common problem in Oracle database - concatenating strings while catering for nulls. This is often encountered when working with data that contains missing or blank values, which can lead to unexpected results if not handled properly.

We will delve into the details of how Oracle handles nulls and provide a solution using the NVL2 function, which allows us to perform conditional concatenation of strings.

Understanding Null Handling in Oracle

Before we dive into the solution, it’s essential to understand how Oracle handles null values. In Oracle, a null value is represented by the special value NULL, which can be distinguished from other data types using various functions and operators.

When working with strings in Oracle, you may encounter null values that need to be handled properly to prevent errors or unexpected results. In this article, we will explore how to concatenate strings while catering for nulls.

The Problem

The given SQL query has the following script:

SELECT 
  siteid,
  address1,
  address2,
  address3,
  address4,
  address5
FROM tblsites;

This query may return data like this:

| SITEID | ADDRESS1 | ADDRESS2 | ADDRESS3 | ADDRESS4 | ADDRESS5 |
|--------|----------|----------|----------|----------|----------|
|    123 | 1 New Street | NULL     | New Town   | NULL       | Newvile |
|    456 | 2 Elm Road   | NULL     | NULL       | New York   | New York |

As you can see, the ADDRESS2 and ADDRESS4 columns contain null values. We need to concatenate these strings with commas while ignoring the null values.

Solution using NVL2

To solve this problem, we will use the NVL2 function, which is a conditional version of the NVL function. The NVL2 function takes two arguments: the first argument is the value to be tested, and the second argument is the value to return if the test value is null.

In our case, we want to concatenate strings only when they are not null. We can use the following query:

SELECT 
  siteid,
  RTRIM(
    NVL2(address1, address1 || ', ', NULL) 
    || NVL2(address2, address2 || ', ', NULL)
    || NVL2(address3, address3 || ', ', NULL)
    || NVL2(address4, address4 || ', ', NULL)
    || NVL2(address5, address5 || ', ', NULL),
    ', '
  ) AS address
FROM tblsites;

Let’s break down this query:

• The NVL2 function is used to test the value of each address column.
• If the address column is not null, we concatenate its value with a comma and return it. If the column is null, we return an empty string (which will be ignored when concatenated).
• We use the || operator to concatenate strings.
• The RTRIM function is used to remove leading whitespace from the final address string.

Results

When we execute this query, we get the following results:

| SITEID | ADDRESS    |
|--------|------------|
| 123    | 1 New Street, New Town, Newvile   |
| 456    | 2 Elm Road, New York, New York      |

As you can see, the null values have been ignored when concatenating the strings with commas.

Conclusion

In this article, we explored how to concatenate strings while catering for nulls in Oracle database. We used the NVL2 function to perform conditional concatenation of strings and demonstrated its usage with an example query. By following this approach, you can ensure that your queries produce accurate results even when dealing with missing or blank values.

Additional Tips

• When working with null values, it’s essential to understand how Oracle handles them.
• Use the NVL function instead of NVL2 if you want to perform non-conditional concatenation of strings. The NVL function returns a value only if the test value is null; otherwise, it returns the first argument.
• Always use the RTRIM function when concatenating strings to remove leading whitespace from the final result.

SQL Fiddle

You can try out this query in an Oracle 11g R2 schema using this SQL Fiddle. Simply copy and paste the code into a new schema, replace the table name with your own, and execute the query to see the results.

CREATE TABLE tblsites ( siteid, address1, address2, address3, address4, address5 ) AS
  SELECT 123, '1 New Street', CAST( NULL AS VARCHAR2(50) ), 'New Town', NULL, 'Newvile' FROM DUAL UNION ALL
  SELECT 456, '2 Elm Road', NULL, NULL, 'New York', 'New York' FROM DUAL;

SELECT 
  siteid,
  RTRIM(
    NVL2(address1, address1 || ', ', NULL) 
    || NVL2(address2, address2 || ', ', NULL)
    || NVL2(address3, address3 || ', ', NULL)
    || NVL2(address4, address4 || ', ', NULL)
    || NVL2(address5, address5 || ', ', NULL),
    ', '
  ) AS address
FROM tblsites;

Note: You can run this query directly in your Oracle database client or using SQL Fiddle.

Last modified on 2023-06-17