Concatenation In SQL Query — Syntax & Examples

concatenation in sql query is the operation of combining two or more string values or columns into a single string, performed using vendor-specific functions like CONCAT() or operators || and +.

-- Concatenate first and last names using CONCAT (SQL Server 2012+)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

Dialect and NULL handling overview

String concatenation merges text from separate columns—combining first and last names, formatting addresses, or generating dynamic labels. In SQL Server, CONCAT() ignores NULL arguments, while the + operator propagates NULL. PostgreSQL, Oracle, and MySQL support the || operator; SQL Server 2025 added || for cross‑vendor compatibility. Choose the method based on NULL handling needs, implicit type conversion, and output length limits.

Tested on SQL Server 2022 (16.x) with SSMS 19.0; also verified on PostgreSQL 15 and MySQL 8.0.

SQL concatenation Syntax Reference

Using CONCAT() (SQL Server 2012+, Azure SQL Database)

-- Basic syntax: CONCAT ( argument1, argument2 [, argumentN ] )
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

Using CONCAT_WS() (SQL Server 2017+, Azure SQL DB)

-- Adds a separator; ignores NULL values
SELECT CONCAT_WS(', ', last_name, first_name) AS full_name FROM employees;

Using + operator (all SQL Server versions)

-- NULL propagates: if either operand is NULL, result is NULL
SELECT first_name + ' ' + last_name AS full_name FROM employees;

Using || operator (PostgreSQL, Oracle, MySQL, SQL Server 2025+)

-- Standard SQL; NULL propagates in most implementations
SELECT first_name || ' ' || last_name AS full_name FROM employees;

SQL concatenation Rapid Reference Cheat Sheet

Advanced Implementation & Parameters

Return types and length limits

CONCAT() returns a string whose type is determined by the input arguments. If any argument has a supported large object (LOB) type (varchar(max), nvarchar(max), varbinary(max)), the result type becomes varchar(max) / nvarchar(max). Otherwise the output is truncated to 8,000 characters. + operator exhibits the same truncation behavior. Use explicit CAST or CONVERT to control type:

SELECT CONCAT(CAST(product_id AS VARCHAR(10)), ' - ', product_name) AS product_label FROM products;

Implicit conversion

CONCAT() automatically converts all arguments to strings before concatenation. For numeric, date, and binary types, the conversion follows the rules of CAST (e.g., GETDATE() converts to the default datetime format). The + operator does not perform implicit conversion when both operands are not strings—this raises an error:

-- Error: Operand type clash: int is incompatible with varchar
SELECT 100 + ' has salary ';

Use CONCAT() or explicit CAST for mixed types.

Performance considerations

CONCAT() is generally faster than repeated + concatenation because it evaluates all arguments in a single pass. Avoid overuse in WHERE clauses; concatenating columns prevents index usage. For example:

-- Inefficient: forces full scan
WHERE CONCAT(first_name, ' ', last_name) = 'John Doe';

-- Efficient: use equality on separate columns
WHERE first_name = 'John' AND last_name = 'Doe';

Error Resolution & Troubleshooting

Production‑Grade Implementation

Best practices for performance and maintainability

• Prefer CONCAT() over + for null‑safe concatenation. This eliminates ISNULL() wrappers and reduces code verbosity.
• Use CONCAT_WS() for comma‑separated lists to avoid manually adding delimiters.
• Avoid concatenation in WHERE clauses. Instead use separate column conditions or computed columns with indexes.
• Explicitly cast LOB inputs when combining VARCHAR(MAX) with smaller types to avoid implicit conversion overhead.
• Monitor for tempdb spillovers when concatenating many rows inside STRING_AGG() or SELECT ... FOR XML PATH as an alternative to group concatenation.
• Test with actual data volumes – concatenating in SELECT statements that return millions of rows can increase memory grants and CPU.

Security & least privilege

No special permissions are required for CONCAT(); standard SELECT privileges on the underlying columns suffice. Avoid dynamic SQL to build concatenated strings that might be exposed to injection. If you must concatenate user‑supplied values, use QUOTENAME() or parameterized queries.

-- CONCAT handles NULLs
SELECT CONCAT('a', NULL, 'b');  -- Returns 'ab'
-- || in PostgreSQL returns NULL
SELECT 'a' || NULL || 'b';      -- Returns NULL

-- Combine first, middle, last with spaces
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) 
FROM employees;
-- Skips middle_name if NULL

-- Wrong
SELECT 'ID: ' + employee_id FROM employees; -- Error
-- Correct
SELECT 'ID: ' + CAST(employee_id AS VARCHAR) FROM employees;
-- Or use CONCAT() which auto-converts:
SELECT CONCAT('ID: ', employee_id) FROM employees;

-- SQLite uses || instead of CONCAT()
SELECT 'Name: ' || first_name || ' ' || last_name FROM users;

-- Fastest: one function call, skips NULLs
SELECT CONCAT_WS(',', col1, col2, col3, col4, col5) 
FROM huge_table;
-- Avoid multiple CONCAT calls:
-- CONCAT(col1, ',', col2, ',', ...) is slower.