SQL - CONCAT() / || - Concatenate Strings
• SQL provides two primary methods for string concatenation: the CONCAT() function (ANSI standard) and the || operator (supported by most databases except SQL Server)
Key Insights
• SQL provides two primary methods for string concatenation: the CONCAT() function (ANSI standard) and the || operator (supported by most databases except SQL Server) • CONCAT() automatically handles NULL values by treating them as empty strings, while || operator propagates NULLs in most databases, requiring explicit COALESCE() handling • Performance differences between methods are negligible in modern databases, but operator support varies significantly across MySQL, PostgreSQL, Oracle, and SQL Server
Understanding String Concatenation Methods
String concatenation in SQL combines two or more string values into a single string. While seemingly straightforward, the implementation varies across database systems, and understanding these differences prevents portability issues and unexpected NULL behavior.
The CONCAT() function accepts multiple arguments and is part of the SQL standard:
SELECT CONCAT('Hello', ' ', 'World') AS greeting;
-- Result: 'Hello World'
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;
The || operator provides a more concise syntax:
SELECT 'Hello' || ' ' || 'World' AS greeting;
-- Result: 'Hello World'
SELECT first_name || ' ' || last_name AS full_name
FROM employees;
NULL Handling Differences
The critical difference between these methods lies in NULL handling. CONCAT() treats NULL as an empty string, while || typically returns NULL if any operand is NULL.
-- CONCAT() with NULL
SELECT CONCAT('John', NULL, 'Doe') AS name;
-- Result: 'JohnDoe'
-- || operator with NULL (PostgreSQL, Oracle)
SELECT 'John' || NULL || 'Doe' AS name;
-- Result: NULL
-- Handling NULLs with COALESCE
SELECT first_name || ' ' || COALESCE(middle_name, '') || ' ' || last_name
FROM employees;
-- Or using CONCAT to avoid NULL issues
SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name)
FROM employees;
This behavior makes CONCAT() safer for dynamic concatenation where NULL values might exist:
CREATE TABLE contacts (
id INT PRIMARY KEY,
first_name VARCHAR(50),
middle_name VARCHAR(50),
last_name VARCHAR(50),
suffix VARCHAR(10)
);
INSERT INTO contacts VALUES
(1, 'John', 'Michael', 'Smith', 'Jr.'),
(2, 'Jane', NULL, 'Doe', NULL),
(3, 'Robert', 'James', 'Johnson', NULL);
-- CONCAT handles NULLs gracefully
SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name, ' ', suffix) AS full_name
FROM contacts;
-- Results preserve non-NULL values without extra spaces
-- || requires explicit NULL handling
SELECT first_name || ' ' ||
CASE WHEN middle_name IS NOT NULL THEN middle_name || ' ' ELSE '' END ||
last_name ||
CASE WHEN suffix IS NOT NULL THEN ' ' || suffix ELSE '' END AS full_name
FROM contacts;
Database-Specific Implementations
MySQL
MySQL supports both CONCAT() and || operator, but || behavior depends on the SQL mode:
-- Default behavior: || is OR operator
SELECT 'Hello' || 'World'; -- Returns 0 (false)
-- Enable PIPES_AS_CONCAT mode
SET sql_mode = 'PIPES_AS_CONCAT';
SELECT 'Hello' || 'World'; -- Returns 'HelloWorld'
-- CONCAT is always safe in MySQL
SELECT CONCAT('Hello', 'World'); -- Returns 'HelloWorld'
-- CONCAT_WS (with separator) is MySQL-specific
SELECT CONCAT_WS(', ', 'Apple', 'Banana', 'Cherry') AS fruits;
-- Result: 'Apple, Banana, Cherry'
-- CONCAT_WS ignores NULL values
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM employees;
PostgreSQL
PostgreSQL fully supports both methods with standard behavior:
-- Both methods work identically (except NULL handling)
SELECT 'PostgreSQL' || ' ' || 'Database' AS method1;
SELECT CONCAT('PostgreSQL', ' ', 'Database') AS method2;
-- String aggregation with STRING_AGG
SELECT department_id,
STRING_AGG(employee_name, ', ' ORDER BY employee_name) AS team_members
FROM employees
GROUP BY department_id;
-- Complex concatenation with formatting
SELECT
first_name || ' ' || last_name ||
' (' || email || ')' AS contact_info
FROM users
WHERE email IS NOT NULL;
Oracle
Oracle uses || as the primary concatenation operator:
-- Standard Oracle concatenation
SELECT first_name || ' ' || last_name AS full_name
FROM employees;
-- CONCAT in Oracle accepts only 2 arguments
SELECT CONCAT(first_name, last_name) AS name
FROM employees;
-- Nested CONCAT for multiple strings
SELECT CONCAT(CONCAT(first_name, ' '), last_name) AS full_name
FROM employees;
-- NULL handling with NVL
SELECT first_name || ' ' || NVL(middle_name, '') || ' ' || last_name
FROM employees;
SQL Server
SQL Server uses + operator instead of ||:
-- SQL Server concatenation
SELECT first_name + ' ' + last_name AS full_name
FROM employees;
-- CONCAT function (SQL Server 2012+)
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;
-- CONCAT_WS (SQL Server 2017+)
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM employees;
-- + operator returns NULL if any operand is NULL
SELECT first_name + ' ' + middle_name + ' ' + last_name AS full_name
FROM employees; -- Returns NULL if middle_name is NULL
-- Using ISNULL for NULL handling
SELECT first_name + ' ' + ISNULL(middle_name + ' ', '') + last_name
FROM employees;
Practical Applications
Building Dynamic SQL Queries
-- Creating search filters dynamically
SELECT
product_id,
CONCAT(category, ' - ', subcategory, ' - ', product_name) AS full_description,
CONCAT('$', CAST(price AS VARCHAR)) AS formatted_price
FROM products
WHERE CONCAT(LOWER(category), LOWER(subcategory), LOWER(product_name))
LIKE '%electronics%';
Generating Reports
-- Address formatting
SELECT
customer_id,
CONCAT_WS(', ',
street_address,
city,
state,
CONCAT(country, ' ', postal_code)
) AS mailing_address
FROM customers;
-- Email template generation
SELECT
CONCAT(
'Dear ', first_name, ',\n\n',
'Your order #', order_id, ' has been shipped.\n',
'Tracking: ', tracking_number
) AS email_body
FROM orders
WHERE status = 'shipped';
Data Migration and ETL
-- Creating unique identifiers
SELECT
CONCAT(
DATE_FORMAT(created_date, '%Y%m%d'),
'-',
LPAD(id, 6, '0')
) AS reference_number
FROM transactions;
-- Combining data from multiple columns
INSERT INTO legacy_system (full_address)
SELECT CONCAT_WS(' | ', address_line1, address_line2, city, state, zip)
FROM modern_system
WHERE address_line1 IS NOT NULL;
Performance Considerations
Concatenation operations are generally fast, but excessive use in WHERE clauses prevents index usage:
-- Avoid: Prevents index on first_name and last_name
SELECT * FROM employees
WHERE CONCAT(first_name, ' ', last_name) = 'John Smith';
-- Better: Use separate conditions
SELECT * FROM employees
WHERE first_name = 'John' AND last_name = 'Smith';
-- If concatenated searches are required, use computed columns
ALTER TABLE employees
ADD full_name AS CONCAT(first_name, ' ', last_name) PERSISTED;
CREATE INDEX idx_full_name ON employees(full_name);
Choose CONCAT() for portability and automatic NULL handling. Use || or database-specific operators when working within a single database system where conciseness matters. Always test NULL behavior in your specific database before deploying concatenation logic to production.