String Concatenation and Formatting in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this String Functions (LENGTH, UPPER, LOWER, TRIM, SUBSTRING)
Builds toward STRING_AGG and ARRAY_AGG
What is String Concatenation in SQL?
PostgreSQL has three ways to join strings together — the || operator, the CONCAT family, and FORMAT — and the choice between them matters whenever a column might be NULL.
You're building a display label for each customer: their name and country, formatted for a report. Some customers are missing a country. Whether the result is NULL, partial, or gracefully filled depends entirely on which tool you use.
What does the || operator do in PostgreSQL?
|| is the simplest: it concatenates two values with a binary operator. But it follows the standard NULL propagation rule — if either side is NULL, the result is NULL. A missing country means the entire label comes back NULL:
SELECT
name || ' (' || country || ')' AS customer_label
FROM customers
LIMIT 5If country is NULL for any customer, that row returns NULL for customer_label. No error — just a missing value where you expected a string.
How does CONCAT handle NULL values?
CONCAT fixes this. It accepts any number of arguments and treats NULL inputs as empty strings:
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customersIf last_name is NULL, CONCAT skips it and returns just the first name. This is usually what you want when building display values from optional fields.
How do you join strings with a separator in SQL?
CONCAT_WS (concatenate with separator) takes a separator as its first argument and joins all non-NULL values with that separator between them — no doubled separators, no trailing delimiter:
SELECT CONCAT_WS(', ', last_name, first_name, middle_name) AS full_name
FROM customersIf middle_name is NULL, the output is 'Smith, John' — not 'Smith, John, '. The separator only appears between values that actually exist.
How do you build a string from a template with FORMAT?
FORMAT works from a template. You write a string with %s placeholders and provide the values to fill them in order:
SELECT FORMAT('Order #%s placed on %s', id, ordered_at::date) AS order_label
FROM ordersFORMAT coerces each argument to text automatically, so you don't need to cast numbers or dates first. NULL arguments are inserted as empty strings. FORMAT is especially useful when building a label with fixed structure — if you find yourself writing long || chains with multiple casts and literal strings, FORMAT is usually cleaner.
One practical difference: CONCAT_WS with a separator handles variable-length data (unknown number of non-NULL fields) cleanly. FORMAT is better when the output has fixed structure with a known set of placeholders.
Why does concatenating with || return NULL?
The one thing that trips people up: || with NULL produces NULL, not a partial string.
If you're building a label from multiple columns and any might be NULL, use CONCAT or CONCAT_WS instead of ||. The || operator is the right choice when you know the inputs are not NULL, or when you want NULL to propagate intentionally — for example, when a NULL in one field means the entire record is incomplete and the label should be absent.
Practice String Concatenation in SQL
Brightlane's reporting system builds order reference strings by combining a prefix and an order identifier.
Write a query to return the result of concatenating 'ORDER-' and '1042' with the || operator.
Output:
- A single row with one column,
order_ref, containing the concatenated string.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 String Concatenation practice problems
Write a query to return the result of concatenating 'ORDER-' and '1042' with the || operator.
Write a query to return the result of using CONCAT to combine 'Hello', ', ', and 'Alex' into a single string.
Write a query to return the result of combining 'Toronto', 'Ontario', and 'Canada' with CONCAT_WS using ', ' as the separator.
Write a query to return the result of concatenating 'Order-' with a SQL NULL using the || operator.
Write a query to return the result of using CONCAT to combine 'Sarah' with a SQL NULL.
Write a query to return the result of applying FORMAT to the template 'Order %s placed on %s' with values '1042' and '2024-03-15'.
Write a query to return the result of combining '42 Main Street', a SQL NULL unit, and 'Toronto' with ', ' as the separator.
Write a query to return two values in a single row: the output of concatenating 'Customer: ' and a SQL NULL using the || operator, and the output of passing those same values to CONCAT.
Write a query to return the search key for the author ' DR. JAMES WATSON ' and the year '1953'.
Start learning to practice all 9 String Concatenation problems, with instant grading and mastery tracking.
Deeper guides on String Concatenation
- joining values from many rows into one string
string_agg needs a delimiter, and the one-argument call fails with an error about the function not existing.
Common questions about String Concatenation
Can you concatenate two numbers with the double pipe operator?
Not on their own. Two integers side by side raise an operator does not exist error, because the operator needs at least one text operand to know what you meant. Cast one of them to text and it works.
Does CONCAT ever return NULL?
No. It treats NULL arguments as empty strings, so concatenating nothing but NULLs gives an empty string rather than NULL. That is the behaviour that makes it the safer choice for building a label from optional fields.
What does FORMAT do with a NULL argument?
It inserts an empty string and carries on, rather than making the whole result NULL. Combined with automatic conversion of numbers and dates to text, that makes FORMAT the tidiest option when the output has a fixed shape and some fields may be missing.
