The SQL concatenation operator (||) is used to combine two or more strings, column values or literals into a single string. It helps create readable output by joining values or adding text to query results.

• Combines values from multiple columns.
• Adds text or numeric literals to query results.
• Creates formatted output, such as full names or descriptive messages.

Syntax:

SELECT column_1 || column_2 AS new_column
FROM table_name;

• column_1 and column_2 are the columns you want to concatenate.
• AS new_column assigns a name to the concatenated result.

Example 1: Concatenating First Name & Last Name

We will create a demo SQL table named my_table containing employee details such as id, first_name, last_name and salary.

Query:

SELECT id, first_name, last_name,
       first_name || last_name AS full_name,
       salary
FROM my_table;

Output:

Explanation: This query combines the first_name and last_name columns using the concatenation operator to create a full_name column. The output displays each employee’s complete name alongside their salary.

Example 2: Concatenating Strings with a Literal

The my_table contains employee details, including id, first_name, last_name and salary. This query uses a string literal to combine an employee's name with their salary information.

Query:

SELECT id, first_name, last_name, salary,
       first_name || ' has salary ' || salary AS new
FROM my_table;

Output:

Explanation: This query uses the concatenation operator (||) to combine the first_name and salary columns with the string literal ' has salary '. The result displays the employee's name along with their salary, such as James has salary 45000.

Example 3: Using a Number Literal in Concatenation

The my_table contains employee details. This query uses a number literal and a string literal with column values to create a combined string using the concatenation operator.

Query:

SELECT id, first_name, last_name, salary,
       first_name || 100 || ' has id ' || id AS new
FROM my_table;

Output:

Explanation: This query concatenates a numeric literal (100) and a character literal (' has id ') with the first_name and id columns. The output provides a readable format that shows each employee's ID along with a custom message.