SQL | Concatenation Operator (original) (raw)
Last Updated : 6 Jan, 2025
The **SQL concatenation operator (||) is a powerful feature that allows us to merge two or more strings into a single output. It is widely used to link columns, **character strings, and **literals in **SQL queries. This operator makes it easier to format and present data in a **user-friendly way, combining multiple values into a readable format.
In this guide, we will explain the **SQL concatenation operator, its **syntax, **use cases, and **practical examples to help us write more efficient and readable SQL queries.
What is the SQL Concatenation Operator?
The concatenation operator in SQL is used to **combine two or **more strings or columns into a single string. It is especially useful for creating meaningful outputs by joining text values from different columns or adding literals (static text) to the output.
- Combines **multiple columns or **strings into one.
- Works with different data types (text, numbers, dates).
- Enhances query output by making it more readable.
**Syntax:
SELECT column1 || column2 AS new_column
FROM table_name;
**Key Terms:
column1andcolumn2are the columns you want to concatenate.AS**new_column**assigns a name to the concatenated result.
Example 1: Concatenating First Name and Last Name
The myTable contains employee details such as id, first_name, last_name, and salary. The columns first_name and last_name are used to store the names of employees.
**Query:
SELECT id, first_name, last_name, first_name || last_name AS full_name, salary
FROM myTable;
**Output:
Concatenation Operator Example 1
**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 myTable contains **employee details, including id, first_name, last_name, and salary. The query adds a character literal to provide more context to the salary information.
**Query:
SELECT id, first_name, last_name, salary,
first_name||' has salary '||salary as "new"
FROM myTable
**Output:
| ID | FIRST_NAME | LAST_NAME | SALARY | NEW |
|---|---|---|---|---|
| 1 | Rajat | Rawat | 10000 | Rajat has salary 10000 |
| 2 | Geeks | ForGeeks | 20000 | Geeks has salary 20000 |
| 3 | Shane | Watson | 50000 | Shane has salary 50000 |
| 4 | Kedar | Jadhav | 90000 | Kedar has salary 90000 |
**Explanation:
This query uses the **concatenation operator to combine the first_name and salary columns with a character literal (' has salary '). The result is a meaningful sentence that provides context to the employee's salary.
Example 3: Using a Number Literal in Concatenation
The myTable contains **details of employees, and this query demonstrates how to concatenate a number literal along with column values.
**Query:
SELECT id, first_name, last_name,
salary, first_name || 100 || ' has id ' || id AS new
FROM myTable;
**Output:
| ID | FIRST_NAME | LAST_NAME | SALARY | NEW |
|---|---|---|---|---|
| 1 | Rajat | Rawat | 10000 | Rajat100 has id 1 |
| 2 | Geeks | ForGeeks | 20000 | Geeks100 has id 2 |
| 3 | Shane | Watson | 50000 | Shane100 has id 3 |
| 4 | Kedar | Jadhav | 90000 | Kedar100 has id 4 |
**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.
Conclusion
The SQL **concatenation operator (||) is a flexible tool that simplifies the process of **combining strings and **columns in queries. It improves the readability of query results by allowing us to **add context and **formatting directly in SQL statements. By using concatenation effectively, we can create meaningful outputs without the need for **additional formatting in our **application code. Understanding how to **handle literals, NULL values, and **complex concatenations will make our SQL queries more efficient and user-friendly.