SQL CONCAT() Function: Syntax, Usage, and Examples

The SQL CONCAT() function combines two or more strings into one. It is commonly used for merging first and last names, formatting values, or dynamically generating messages inside SQL queries. The CONCAT syntax is supported by most relational databases including MySQL, PostgreSQL, and SQL Server.

How to Use SQL CONCAT

The basic form of CONCAT joins strings together in the order provided:

SELECT CONCAT(string1, string2, string3, ...);

Each argument is a string or expression. The function returns a single string with all inputs joined together.

Example

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

This example merges three strings into a single one, with a space in the middle.

When to Use CONCAT in SQL Queries

The CONCAT function is essential when you need to:

• Combine the first and last name columns into a full name
• Add labels to numeric or date fields
• Build dynamic messages from multiple data sources
• Format data in reports or exports
• Create identifiers or keys by combining column values

Generating Full Names

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

This merges first and last names into a single readable format.

Creating Dynamic Messages

SELECT CONCAT('User ', username, ' joined on ', signup_date) AS message
FROM users;

This SQL CONCAT query builds readable messages for each user record.

Examples of CONCAT

Example 1: Basic String Concatenation

SELECT CONCAT('SQL', '-', 'Tutorial') AS result;
-- Output: SQL-Tutorial

This basic CONCAT example shows how the function works with literal values.

Example 2: Concatenating Column Values

SELECT CONCAT(city, ', ', state) AS location
FROM addresses;

Combines two columns with a comma separator.

Example 3: CONCAT with NULL Values

In most SQL dialects, CONCAT() treats NULL as an empty string:

SELECT CONCAT('Order #', NULL, 123);
-- Output: Order #123

Unlike the || operator, CONCAT doesn't result in NULL when one argument is NULL. This makes it more reliable for real-world data with missing fields.

Example 4: CONCAT in SQL Server

In SQL Server, CONCAT SQL works the same way:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

If you're using older SQL Server versions (pre-2012), you may need to use + instead:

SELECT first_name + ' ' + last_name AS full_name
FROM employees;

Be aware that + will return NULL if any operand is NULL.

Learn More About CONCAT

CONCAT in SQL Query with Numbers and Strings

CONCAT can handle numbers and other data types by implicitly converting them to strings:

SELECT CONCAT('Total: $', price)
FROM products;

You can also wrap numbers in CAST() if needed:

SELECT CONCAT('Score: ', CAST(score AS VARCHAR))
FROM results;

Combining CONCAT with Other Functions

You can nest functions inside CONCAT() for more dynamic results:

SELECT CONCAT('The price is $', ROUND(price, 2))
FROM items;

This formats numbers while building the string.

Or use with COALESCE() to handle missing values:

SELECT CONCAT('Email: ', COALESCE(email, 'N/A'))
FROM contacts;

This avoids blank spots when email is missing.

CONCAT with Table Joins

Often used in joined queries to make combined values more meaningful:

SELECT CONCAT(c.first_name, ' ', c.last_name, ' - ', o.order_id) AS customer_order
FROM customers c
JOIN orders o ON c.id = o.customer_id;

This gives a readable label per order.

SQL Server CONCAT vs CONCAT_WS

In SQL Server and MySQL, CONCAT_WS ("with separator") offers an alternative syntax:

SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM people;

The first argument is used as the separator. NULL values are skipped automatically.

String Concat with CASE Statements

To conditionally format strings:

SELECT CONCAT(
  'Status: ',
  CASE
    WHEN status = 'A' THEN 'Active'
    WHEN status = 'I' THEN 'Inactive'
    ELSE 'Unknown'
  END
) AS status_label
FROM accounts;

This combines logic and string output in one field.

CONCAT Unique Identifiers

When creating composite keys:

SELECT CONCAT(country_code, '-', city_code) AS location_id
FROM locations;

This helps create readable, unique values for external reporting or internal use.

CONCAT and Sorting

If you want to sort based on a concatenated value:

SELECT CONCAT(last_name, ', ', first_name) AS name
FROM users
ORDER BY name;

You can also use the expression in ORDER BY directly without alias:

ORDER BY CONCAT(last_name, ', ', first_name);

CONCAT vs || Operator

Some databases (like PostgreSQL) use the || operator instead of CONCAT():

SELECT first_name || ' ' || last_name AS full_name
FROM users;

However, || returns NULL if any value is NULL, unlike SQL CONCAT, which treats NULL as an empty string. This makes CONCAT more resilient in most use cases.

Performance Considerations

• CONCAT() is lightweight and optimized for string operations.
• Use indexes on columns being concatenated only if you're filtering, not just for display.
• Avoid overusing CONCAT in WHERE clauses—it can prevent index usage:

-- Not recommended
WHERE CONCAT(first_name, ' ', last_name) = 'Jane Smith';

-- Better
WHERE first_name = 'Jane' AND last_name = 'Smith';