How to Concatenate in SQL

Use concatenation when multiple text values should be combined into one readable output. This is perfect for full names, formatted labels, addresses, export strings, and report display fields.

What you’ll build or solve

You’ll learn how to concatenate strings in SQL using CONCAT() and operator-based alternatives. You’ll also know how to handle NULL values safely.

When this approach works best

This approach is the right choice when multiple text columns should appear as one combined field.

Common real-world scenarios include:

• Full names
• Address lines
• Product labels
• Export CSV helpers
• Dashboard display text

This is a bad idea when the combined text should be permanently stored. Prefer generating it at query time unless there is a strong reason to persist it.

Prerequisites

You only need:

• A SQL table with text columns
• Basic SELECT knowledge

Step-by-step instructions

Step 1: Combine columns with CONCAT()

Use CONCAT() to join text values in order.

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

This creates a readable full name.

You can combine multiple pieces.

SELECT CONCAT(city, ', ', country) AS location
FROM offices;

Some databases also support string operators like ||.

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

What to look for:

• CONCAT() combines text in order
• Add separators manually
• Great for display fields
• NULL handling differs by database
• Use aliases for readable output

Examples you can copy

Full name

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

City label

SELECT CONCAT(city, ', ', country) AS location
FROM offices;

Product display

SELECT CONCAT(brand, ' - ', model) AS product_name
FROM products;

Common mistakes and how to fix them

Mistake 1: Forgetting separators

What the reader might do:

SELECT CONCAT(first_name, last_name)

Why it breaks: names run together without spacing.

Corrected approach:

SELECT CONCAT(first_name, ' ', last_name)

Mistake 2: Ignoring NULL values

What the reader might do:

SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name)

Why it breaks: in some databases, one NULL can nullify the whole result.

Corrected approach:

Wrap nullable columns with COALESCE().

COALESCE(middle_name, '')

Mistake 3: Storing derived display strings permanently

What the reader might do:

Save full_name into the table.

Why it breaks: duplicated derived data becomes harder to maintain.

Corrected approach:

Generate it in the query.

Troubleshooting

If text runs together, add separators.

If the result becomes NULL, wrap nullable fields with COALESCE().

If portability matters, confirm whether your database prefers CONCAT() or ||.

If the display field is reused often, create a view.

Quick recap

• Use CONCAT() to combine text
• Add spaces or separators manually
• Handle NULL with COALESCE()
• Use aliases for clean output
• Prefer query-time generation over permanent storage