CONCAT() in SQL

Concatenates two or more strings into a single string.

Syntax

CONCAT(string1, string2, ..., stringN)

Return type

VARCHAR (or equivalent string type in the respective DBMS)

CONCAT() Function Example

SELECT CONCAT('Hello', ' ', 'World');
-- Output: 'Hello World'

The example demonstrates the usage of the CONCAT function in SQL to join multiple strings, resulting in the output 'Hello World'.

What is CONCAT() in SQL

The CONCAT() function in SQL is used to combine two or more strings into a single string. It simplifies string manipulation by automatically handling NULL values, treating them as empty strings instead of returning NULL. Supported in various SQL databases, including SQL Server, MySQL, and PostgreSQL, CONCAT() replaces the traditional + or || operators for string concatenation. This function is particularly useful for formatting output, constructing dynamic queries, and managing textual data efficiently in database operations.

Parameters:

string1, string2, ..., stringN: Strings or columns to be concatenated. Each string must be of a compatible string type (e.g., VARCHAR, TEXT).

Top Use Cases of CONCAT Function

Data Transformation:

Combining first and last names into a full name.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
-- Output:
first_name    last_name    full_name
-----------   ----------   ------------------
John          Smith        John Smith
Maria         Garcia       Maria Garcia
Robert        Johnson      Robert Johnson
Sarah         Williams     Sarah Williams
James         Brown        James Brown

Date Formatting:

Concatenating year, month, and day to form a date string:

SELECT CONCAT(YEAR(date_column), '-', MONTH(date_column), '-', DAY(date_column)) AS formatted_date FROM events;
-- Output:
date_column          formatted_date
-------------------  --------------
2024-03-15 14:30:00  2024-3-15
2024-03-16 09:45:00  2024-3-16
2024-03-17 16:20:00  2024-3-17
2024-03-18 11:10:00  2024-3-18
2024-03-19 13:25:00  2024-3-19

Notes:

Behavior: If any parameter is NULL, the function returns NULL in most databases.
Performance Considerations: No significant performance concerns for typical use cases. Avoid excessive concatenation in large loops for performance.
Version Info: Supported in MySQL 5.x and above.
Deprecated/Recommended Alternatives: If the database supports CONCAT_WS(), consider using it for concatenation with delimiters.