The CONCAT
function in SQL is used to concatenate two or more strings together. It allows you to combine text from different columns or literal values into a single string.
Syntax
The syntax for the CONCAT
function varies slightly between different SQL database systems. Here's a general syntax:
CONCAT(string1, string2, ...)
string1
, string2
, ...: The strings to be concatenated. These can be column values, literal strings, or a combination of both.
Example
Consider a table employees
with columns first_name
and last_name
containing the names of employees.
CREATE TABLE employees (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
INSERT INTO employees (employee_id, first_name, last_name)
VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
(3, 'Alice', 'Johnson');
To concatenate the first_name
and last_name
columns with a space between them:
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;
Output
| full_name |
|-------------|
| John Doe |
| Jane Smith |
| Alice Johnson|
Usage
The CONCAT
function is commonly used for various purposes, including:
- Creating full names from separate first and last name columns.
- Building dynamic SQL queries by concatenating strings with variable values.
- Generating formatted output for reports or user interfaces.
Handling Variations in Syntax
While CONCAT
is widely supported across SQL database systems, some systems may use slightly different syntax or function names. For example:
- MySQL: Use
CONCAT
.
- PostgreSQL: Use the concatenation operator
||
or CONCAT
.
- SQL Server: Use
CONCAT
.
The CONCAT
function in SQL is a powerful tool for combining strings together to create composite values. Whether you need to format output, generate dynamic SQL, or concatenate values for display, understanding how to use CONCAT
effectively is essential for SQL programming and data manipulation tasks.