Oracle CONCAT Function: A Simple Guide to Joining Strings

When working with data in Oracle SQL, you'll often need to combine or "join" strings together. The CONCAT function is a built-in tool specifically for this purpose.

This simple guide will explain what the CONCAT function does, its syntax, and how to use it with practical examples.

What is the CONCAT Function in Oracle?

The CONCAT function (which stands for "concatenate") takes two or more strings as input and returns them as a single, combined string.

It's useful for formatting data, creating reports, or building dynamic labels. For example, you could use it to combine a "first name" column and a "last name" column into a single "full name" string.

It's important to know that the CONCAT function is an alternative to the more common concatenation operator, which is two vertical pipes (||). We'll show both in the examples.

CONCAT Function Syntax

The syntax for the CONCAT function is very simple:

CONCAT(string1, string2, [string3, ...])

Let's break that down:

• string1, string2, string3, etc.: These are the strings you want to join. You must provide at least two, but you can provide many. The arguments can be:
  • Static text (like 'Hello').
  • Column names (like first_name).
  • A mix of both.

Oracle CONCAT Function Examples

Here are two practical examples of how to use CONCAT in your Oracle SQL queries.

Example 1: Joining Simple Text Strings

This is the most basic use of CONCAT. Let's join three separate strings, 'Hello', ' ' (a space), and 'World', into one.

We use the DUAL table for this test, which is a special one-row, one-column table in Oracle perfect for quick tests.

Query:

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

Result:

GREETING
-----------
Hello World

Example 2: Combining Columns and Text

A more common use is to combine static text with data from your tables. Let's use the employees table as an example. We want to create a descriptive string for an employee, combining their last_name and job_id.

Query:

-- This query finds the employee with ID 152
-- It combines the last name, the text "'s job is: ", and the job_id
-- Note: '''s' is the special way to write 's (a single quote) in a string
SELECT CONCAT(last_name, '''s job is: ', job_id) AS Job_Description
FROM employees
WHERE employee_id = 152;

Result:

JOB_DESCRIPTION
------------------------------
Hall's job is: SA_REP

Quick Note: CONCAT vs. the || Operator

You can achieve the exact same results using the || operator. Many developers prefer this method as it can be easier to read.

Here is Example 2, rewritten using ||:

SELECT last_name || '''s job is: ' || job_id AS Job_Description
FROM employees
WHERE employee_id = 152;

Both CONCAT() and || are correct; it's a matter of personal or team preference.