How to Use CONCAT in MySQL
String concatenation is a fundamental operation in database queries. MySQL's CONCAT function combines two or more strings into a single string, enabling you to format data directly in your SQL...
Key Insights
- CONCAT returns NULL if any argument is NULL, which catches many developers off guard—use CONCAT_WS or COALESCE to handle NULL values gracefully
- CONCAT_WS (with separator) eliminates repetitive separator strings and automatically skips NULL values, making it superior for most multi-field concatenation tasks
- Concatenating columns in WHERE clauses prevents index usage and kills query performance—filter first, then concatenate in the SELECT clause
Introduction to CONCAT
String concatenation is a fundamental operation in database queries. MySQL’s CONCAT function combines two or more strings into a single string, enabling you to format data directly in your SQL queries rather than handling it in application code.
You’ll reach for CONCAT when building user-friendly displays, generating composite identifiers, formatting reports, or constructing dynamic content. Common scenarios include combining first and last names, assembling full addresses, creating product SKUs from component parts, or building formatted labels for dropdown menus.
The basic syntax is straightforward:
SELECT CONCAT(string1, string2, string3, ...);
CONCAT accepts any number of string arguments and returns them joined together. Arguments can be literal strings, column values, or expressions that evaluate to strings. MySQL automatically converts non-string data types to strings during concatenation.
Basic CONCAT Syntax and Simple Examples
Let’s start with literal strings:
SELECT CONCAT('Hello', ' ', 'World') AS greeting;
-- Result: "Hello World"
The real power emerges when combining column values. Consider a users table:
SELECT
CONCAT(first_name, ' ', last_name) AS full_name
FROM users;
This produces clean, formatted full names. You can add punctuation and formatting:
SELECT
CONCAT(last_name, ', ', first_name) AS formal_name
FROM users;
-- Result: "Smith, John"
Concatenate multiple columns with literals to create complex strings:
SELECT
CONCAT('User #', user_id, ': ', first_name, ' ', last_name) AS user_label
FROM users;
-- Result: "User #1234: John Smith"
You can also nest functions within CONCAT:
SELECT
CONCAT(UPPER(first_name), ' ', UPPER(last_name)) AS shouting_name
FROM users;
-- Result: "JOHN SMITH"
CONCAT vs CONCAT_WS (With Separator)
CONCAT_WS (CONCAT With Separator) is a superior alternative when you’re joining multiple values with the same separator. The first argument is the separator, followed by the values to concatenate.
Standard CONCAT requires repeating the separator:
SELECT
CONCAT(city, ', ', state, ', ', zip_code) AS location
FROM addresses;
CONCAT_WS eliminates this repetition:
SELECT
CONCAT_WS(', ', city, state, zip_code) AS location
FROM addresses;
The real advantage appears when dealing with optional fields. CONCAT_WS automatically skips NULL values while maintaining consistent separators:
SELECT
CONCAT_WS(', ', address_line1, address_line2, city, state, zip_code) AS full_address
FROM addresses;
If address_line2 is NULL, CONCAT_WS produces “123 Main St, Springfield, IL, 62701” without an extra comma. Standard CONCAT would require complex CASE statements or multiple COALESCE calls to achieve this.
For CSV-style output:
SELECT
CONCAT_WS(',', product_id, product_name, category, price) AS csv_row
FROM products;
-- Result: "101,Widget,Hardware,29.99"
Building structured data like email headers:
SELECT
CONCAT_WS(' | ', department, employee_name, extension) AS directory_entry
FROM employees;
-- Result: "Sales | Jane Doe | x1234"
Handling NULL Values
This is where CONCAT trips up developers. If ANY argument is NULL, CONCAT returns NULL:
SELECT CONCAT('Hello', NULL, 'World') AS result;
-- Result: NULL
With table data:
-- If middle_name is NULL:
SELECT
CONCAT(first_name, ' ', middle_name, ' ', last_name) AS full_name
FROM users;
-- Result: NULL (not "John Smith")
Use COALESCE to provide default values:
SELECT
CONCAT(
first_name,
' ',
COALESCE(middle_name, ''),
' ',
last_name
) AS full_name
FROM users;
However, this creates extra spaces when middle_name is NULL. A better approach combines COALESCE with conditional logic:
SELECT
CONCAT(
first_name,
CASE WHEN middle_name IS NOT NULL THEN CONCAT(' ', middle_name) ELSE '' END,
' ',
last_name
) AS full_name
FROM users;
Or use CONCAT_WS, which handles NULLs gracefully:
SELECT
CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM users;
-- Automatically skips NULL middle_name
For optional prefixes or suffixes:
SELECT
CONCAT(
IFNULL(CONCAT(title, ' '), ''),
first_name,
' ',
last_name,
IFNULL(CONCAT(', ', suffix), '')
) AS formal_name
FROM users;
-- Result: "Dr. Jane Smith, PhD" or "John Doe"
Practical Real-World Use Cases
Creating Display Names with Titles
SELECT
user_id,
CONCAT_WS(' ', title, first_name, middle_initial, last_name, suffix) AS display_name,
email
FROM users
WHERE status = 'active'
ORDER BY last_name;
Generating Product SKUs
SELECT
CONCAT(
category_code,
'-',
LPAD(subcategory_id, 3, '0'),
'-',
LPAD(product_id, 6, '0')
) AS sku,
product_name
FROM products;
-- Result: "ELC-042-001234"
Building Formatted Address Blocks
SELECT
customer_id,
CONCAT(
address_line1,
CASE WHEN address_line2 IS NOT NULL
THEN CONCAT('\n', address_line2)
ELSE '' END,
'\n',
city, ', ', state, ' ', zip_code,
'\n',
country
) AS mailing_address
FROM customer_addresses;
Creating Email Subject Lines
SELECT
CONCAT(
'[Ticket #', ticket_id, '] ',
status_label,
': ',
LEFT(subject, 50)
) AS email_subject
FROM support_tickets
WHERE assigned_to = 'john.doe@company.com';
-- Result: "[Ticket #5678] Open: Cannot login to dashboard"
Generating URLs or File Paths
SELECT
user_id,
CONCAT(
'https://cdn.example.com/avatars/',
user_id,
'/',
LOWER(CONCAT(first_name, '_', last_name)),
'.jpg'
) AS avatar_url
FROM users;
-- Result: "https://cdn.example.com/avatars/1234/john_smith.jpg"
Creating Search-Friendly Concatenated Fields
SELECT
product_id,
CONCAT_WS(' ',
product_name,
brand,
category,
REPLACE(tags, ',', ' ')
) AS searchable_text
FROM products;
Performance Considerations and Best Practices
Avoid Concatenation in WHERE Clauses
This kills performance by preventing index usage:
-- BAD: Prevents index on first_name and last_name
SELECT * FROM users
WHERE CONCAT(first_name, ' ', last_name) = 'John Smith';
Instead, filter on individual columns:
-- GOOD: Uses indexes
SELECT * FROM users
WHERE first_name = 'John' AND last_name = 'Smith';
Concatenate in SELECT, Not WHERE
-- Efficient approach
SELECT
CONCAT(first_name, ' ', last_name) AS full_name,
email,
created_at
FROM users
WHERE last_name = 'Smith' -- Uses index
AND first_name = 'John'; -- Uses index
Consider Application-Layer Concatenation
For complex formatting or when concatenating many fields, application code may be more maintainable:
-- Database: Return raw data
SELECT first_name, middle_name, last_name, title, suffix
FROM users
WHERE user_id = 1234;
-- Application: Handle formatting
This is especially true when formatting rules change frequently or vary by locale.
Virtual Generated Columns for Frequently Used Concatenations
If you repeatedly concatenate the same fields, create a generated column:
ALTER TABLE users
ADD COLUMN full_name VARCHAR(200)
GENERATED ALWAYS AS (CONCAT_WS(' ', first_name, last_name)) STORED;
CREATE INDEX idx_full_name ON users(full_name);
Now you can efficiently search on the concatenated value:
SELECT * FROM users WHERE full_name LIKE 'John%';
Limit Concatenation Length
Concatenating very long text fields can impact memory and performance:
-- Limit concatenated length
SELECT
CONCAT(
LEFT(title, 100),
' - ',
LEFT(description, 200)
) AS preview
FROM articles;
CONCAT is a fundamental tool for formatting database output. Master CONCAT_WS for cleaner multi-field concatenation, always handle NULL values explicitly, and keep performance in mind by avoiding concatenation in filter conditions. When used appropriately, CONCAT moves formatting logic closer to your data, producing cleaner application code and more efficient queries.