Concatenation in SQL: How to use CONCAT() and CONCAT_WS()

Introduction

SQL (Structured Query Language) is the backbone of database operations, allowing users to retrieve, manipulate, and transform data efficiently. One of the most common string operations in SQL is concatenation—combining multiple strings into a single output.

Why Learn Concatenation?

Data Formatting: Merge first and last names, addresses, or other related fields.
Dynamic SQL: Build queries programmatically.
Report Generation: Create readable output from raw database columns.
API/Export Preparation: Format data for CSV, JSON, or other structured outputs.

In this guide, we’ll explore SQL concatenation in detail, covering:
✔ Core functions (CONCAT, CONCAT_WS)
✔ Database-specific syntax (MySQL, SQL Server, PostgreSQL, Oracle)
✔ Handling NULL values
✔ Real-world use cases
✔ Performance best practices

What is Concatenation in SQL?

Concatenation is the process of joining two or more strings into a single string. Unlike arithmetic operations, concatenation deals with text manipulation.

Key Concepts:

Strings vs. Numbers:
- Concatenation applies to text (VARCHAR, CHAR, TEXT).
- Some databases auto-convert numbers to strings (e.g., CONCAT(10, ' items') → "10 items").
Implicit vs. Explicit Concatenation:
- Explicit: Using functions like CONCAT().
- Implicit: Using operators like + (SQL Server) or || (Oracle, PostgreSQL).
NULL Handling:
- Some functions ignore NULL (e.g., CONCAT_WS), while others propagate it (e.g., || in Oracle unless NVL is used).

Using CONCAT() in SQL

The CONCAT() function merges strings sequentially. It’s widely supported but behaves slightly differently across databases.

Syntax Deep Dive:

CONCAT(string1, string2, ..., stringN)

Arguments: Accepts two or more strings (some databases, like Oracle, limit to two).
Return Type: Typically VARCHAR or TEXT.

Examples with Explanations:

1. Basic Concatenation

-- MySQL/PostgreSQL/SQL Server  
SELECT CONCAT('SQL', ' ', 'Tutorial') AS result;

Output: "SQL Tutorial"
Why? Combines three strings with a space in the middle.

2. Column Concatenation (Full Name)

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

Use Case: Generates a readable name from separate columns.

3. Handling NULLs

-- MySQL/PostgreSQL: NULL treated as empty string  
SELECT CONCAT('Hello', NULL, 'World') AS output; -- "HelloWorld"  

-- SQL Server (same behavior)  
SELECT CONCAT('Hello', NULL, 'World'); -- "HelloWorld"  

-- Oracle (requires NVL or ||)  
SELECT CONCAT('Hello', NVL(NULL, '')) FROM dual; -- "Hello"

Key Insight: Know your database’s NULL behavior to avoid surprises.

Using CONCAT_WS() for Smart Concatenation

CONCAT_WS() (Concatenate With Separator) simplifies adding delimiters and automatically skips NULL values.

Syntax Explained:

CONCAT_WS(separator, string1, string2, ..., stringN)

Separator: Inserted between non-NULL strings (e.g., ', ', '-').
NULL Handling: Ignores NULLs without leaving gaps.

Practical Examples:

1. Address Formatting

SELECT CONCAT_WS(', ', street, city, postal_code) AS address  
FROM customers;

Output: "123 Main St, Springfield, 12345"
Advantage: No extra commas if city is NULL.

2. Generating CSV Data

SELECT CONCAT_WS('|', id, name, price) AS csv_line  
FROM products;

Output: "1|Laptop|999.99"
Tip: Use for export-friendly formats.

3. Dynamic Paths

SELECT CONCAT_WS('/', 'https://example.com', 'img', product_id) AS url  
FROM products;

Output: "https://example.com/img/101"

Database-Specific Concatenation

1. MySQL & PostgreSQL

CONCAT(): Supports unlimited arguments.
CONCAT_WS(): Available in both.

PostgreSQL Bonus: || operator (if enabled):

SELECT 'Hello' || ' ' || 'World'; -- "Hello World"

2. SQL Server

CONCAT(): Ignores NULLs.

+ Operator: NULL propagates (use ISNULL):

SELECT 'Hello' + NULL + 'World'; -- NULL  
SELECT 'Hello' + ISNULL(NULL, '') + 'World'; -- "HelloWorld"

CONCAT_WS(): Added in SQL Server 2017.

3. Oracle

CONCAT(): Only two arguments.

|| Preferred:

SELECT first_name || ' ' || last_name FROM employees;

Handle NULLs with NVL:

SELECT 'Hello' || NVL(NULL, '') || 'World' FROM dual;

Advanced Use Cases

1. Conditional Concatenation

-- Add middle name only if not NULL  
SELECT CONCAT(first_name,  
              CASE WHEN middle_name IS NOT NULL THEN CONCAT(' ', middle_name) ELSE '' END,  
              ' ', last_name) AS full_name  
FROM users;

2. Dynamic SQL Generation

-- MySQL: Build a query string  
SET @table = 'employees';  
SET @sql = CONCAT('SELECT * FROM ', @table, ' WHERE salary > 50000');  
PREPARE stmt FROM @sql;  
EXECUTE stmt;

3. Aggregating Strings (Group Concatenation)

-- MySQL (GROUP_CONCAT)  
SELECT department_id,  
       GROUP_CONCAT(CONCAT(first_name, ' ', last_name) SEPARATOR ', ') AS team  
FROM employees  
GROUP BY department_id;  

-- PostgreSQL (STRING_AGG)  
SELECT department_id,  
       STRING_AGG(CONCAT(first_name, ' ', last_name), ', ') AS team  
FROM employees  
GROUP BY department_id;

Performance Tips

Avoid Over-Concatenation:
- Large strings consume memory. Process in batches if needed.

Indexes Don’t Help:

Filter first, then concatenate:

-- Bad: CONCAT slows down filtering  
SELECT CONCAT(first_name, ' ', last_name) AS name  
FROM users  
WHERE CONCAT(first_name, ' ', last_name) LIKE 'John%';  

-- Good: Filter before concatenating  
SELECT CONCAT(first_name, ' ', last_name) AS name  
FROM users  
WHERE first_name LIKE 'John%';

Use CONCAT_WS() Over Nested CONCAT():
- Cleaner and often faster.

Conclusion

SQL concatenation (CONCAT and CONCAT_WS) is a must-know skill for:
🔹 Data formatting (names, addresses)
🔹 Dynamic SQL (building queries on-the-fly)
🔹 Export/API preparation (CSV, JSON)

Key Takeaways:

✅ CONCAT() merges strings simply; handles NULLs in MySQL/PostgreSQL/SQL Server.
✅ CONCAT_WS() adds separators and skips NULLs—ideal for addresses/CSVs.
✅ Database Differences: Oracle uses ||, SQL Server uses +, etc.
✅ Optimize Performance: Filter before concatenating; avoid excessive operations.

Ready to practice? Try these examples in your database!