What is the string concatenation operator in Oracle?

String Concatenation in Oracle SQL and PL/SQL

Explore the various methods for concatenating strings in Oracle Database, including the standard SQL operator, built-in functions, and PL/SQL techniques.

String concatenation is a fundamental operation in any programming language or database system, allowing you to combine multiple strings into a single, longer string. In Oracle Database, there are several ways to achieve this, each with its own nuances and best-use cases. This article will guide you through the primary methods for string concatenation in both SQL queries and PL/SQL blocks, helping you choose the most appropriate technique for your specific needs.

The Concatenation Operator: ||

The most common and idiomatic way to concatenate strings in Oracle SQL and PL/SQL is by using the double pipe operator, ||. This operator is intuitive and works similarly to concatenation operators in many other programming languages. It can combine any number of strings, literals, or column values into a single string. If any operand is NULL, the NULL value is treated as an empty string, and the concatenation proceeds without resulting in a NULL output, unless all operands are NULL.

SELECT 'Hello' || ' ' || 'World!' AS concatenated_string FROM DUAL;

SELECT first_name || ' ' || last_name AS full_name
FROM employees
WHERE employee_id = 100;

DECLARE
    v_greeting VARCHAR2(100);
    v_name VARCHAR2(50) := 'Alice';
BEGIN
    v_greeting := 'Welcome, ' || v_name || '!';
    DBMS_OUTPUT.PUT_LINE(v_greeting);
END;

Examples of using the || operator in SQL and PL/SQL.

The CONCAT() Function

Oracle also provides the CONCAT() function for string concatenation. However, unlike the || operator, the CONCAT() function is limited to concatenating exactly two arguments. If you need to concatenate more than two strings, you must nest multiple CONCAT() calls, which can quickly become cumbersome and less readable than using the || operator. Like ||, CONCAT() treats NULL arguments as empty strings.

SELECT CONCAT('Hello', ' World!') AS concatenated_string FROM DUAL;

SELECT CONCAT(CONCAT(first_name, ' '), last_name) AS full_name
FROM employees
WHERE employee_id = 100;

Examples of using the CONCAT() function in SQL.

Handling NULL Values and Advanced Concatenation

Understanding how NULL values behave during concatenation is crucial. As mentioned, both || and CONCAT() treat NULLs as empty strings. However, for more complex scenarios, such as concatenating a list of values with a separator while ignoring NULLs, Oracle offers functions like LISTAGG (for SQL) or custom PL/SQL logic.

flowchart TD
    A[Start Concatenation] --> B{Is operand NULL?}
    B -- Yes --> C[Treat as empty string]
    B -- No --> D[Use operand value]
    C --> E[Combine with other strings]
    D --> E
    E --> F[Resulting String]
    F --> G[End]

Flowchart illustrating how Oracle handles NULL values during string concatenation.

SELECT 'Value1' || NULL || 'Value2' AS result_with_null FROM DUAL; -- Result: Value1Value2

SELECT CONCAT('Value1', NULL) AS result_with_concat_null FROM DUAL; -- Result: Value1

-- Using LISTAGG for concatenating multiple values with a separator, ignoring NULLs
SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_name) AS concatenated_list
FROM your_table
WHERE column_name IS NOT NULL;

Examples demonstrating NULL handling and LISTAGG for advanced concatenation.