- Aggregate functions
- ALTER TABLE statement
- AVERAGE function
- BETWEEN operator
- CASE expression
- CAST() function
- COALESCE() function
- Comment
- Common table expression
- CONCAT() function
- Constraints
- CONTAINS
- CONVERT function
- COUNT() function
- CREATE TABLE statement
- CROSS JOIN
- Cursor
- Data types
- Date functions
- DATEADD() function
- DATEDIFF() function
- DELETE statement
- DROP TABLE statement
- EXISTS operator
- FORMAT() function
- GROUP BY statement
- HAVING clause
- IF statement
- Index
- Injection
- INNER JOIN
- INSERT INTO statement
- IS NOT NULL condition
- IS NULL condition
- Joins
- LAG function
- LEFT JOIN
- LENGTH() function
- LIKE operator
- LIMIT clause
- MERGE statement
- Normalization
- Not equal
- Operators
- ORDER BY clause
- Partition
- Pivot table
- Regex
- REPLACE function
- ROUND function
- SELECT DISTINCT clause
- SELECT statement
- Set operators
- Stored procedure
- String functions
- Subquery
- Substring
- Temporary table
- Transaction
- Trigger
- TRUNCATE TABLE
- UPDATE statement
- Views
- WHERE clause
- Window functions
SQL
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 SQL
syntax is supported by most relational databases including MySQL, PostgreSQL, and SQL Server.
How to Use SQL CONCAT
The basic form of CONCAT in SQL
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 sql concat
function is essential when you need to:
- Combine 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 concat in sql query
builds readable messages for each user record.
Examples of CONCAT SQL
Example 1: Basic String Concatenation
SELECT CONCAT('SQL', '-', 'Tutorial') AS result;
-- Output: SQL-Tutorial
This basic sql string 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 in SQL
CONCAT in SQL Query with Numbers and Strings
The concat function in sql
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.
SQL 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.
SQL 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
inWHERE
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';
Sign up or download Mimo from the App Store or Google Play to enhance your programming skills and prepare for a career in tech.