SQL | Concatenation Operator

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:

• column1 and column2 are 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:

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:

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.

FAQs

What is the concatenation operator in SQL?

The concatenation operator (||) in SQL combines two or more strings into a single string. It is commonly used in queries to join text values or columns.

What does ||’ ‘|| mean in SQL?

The ||' '|| operator adds a space between two concatenated strings. It ensures the resulting string has a space separating the combined values.

What is a concatenation operator?

A concatenation operator is a symbol or function that merges multiple strings into one.

GeeksforGeeks

Improve

Article Tags :

• Databases
• Experiences
• SQL
• SQL-Clauses-Operators