CONCAT() Function in SQL

Introduction

CONCAT is a string function used to combine two or more values into one string. It can join text literals, character columns, numbers, dates, and calculated expressions for names, labels, addresses, descriptions, exports, and display values.

SELECT CONCAT('Hello', ' ', 'SQL') AS message;

-- Result: Hello SQL

CONCAT does not understand the meaning of the values it joins. It does not automatically add spaces, punctuation, or labels, and its NULL and conversion behavior varies among database systems. The query author must define every separator and fallback deliberately.

Concatenation is valuable at presentation boundaries, but it should not replace structured columns, composite keys, typed dates, or numeric values. Good SQL keeps data structured during filtering and calculation and creates combined text only where a consumer actually needs it.

Basic CONCAT Syntax

The common form accepts a list of values in the order they should appear. The exact minimum argument count and supported types depend on the DBMS.

CONCAT(value1, value2, value3, ...)

Every literal separator is an argument. If no separator is supplied, values are placed directly beside each other.

SELECT CONCAT('Hello', 'World') AS without_space,
       CONCAT('Hello', ' ', 'World') AS with_space;

Concatenating Multiple Strings

CONCAT can accept several string literals and expressions. Formatting the call across lines makes long argument lists easier to review.

SELECT CONCAT(
         'Learn',
         ' ',
         'SQL',
         ' ',
         'Functions'
       ) AS lesson_title;

-- Result: Learn SQL Functions

The engine evaluates each argument and joins the resulting text in order. Reordering arguments changes the output.

CONCAT with Columns

Column values can be combined for each selected row. A full-name display is a common introductory example.

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

This assumes both values are present and that the desired cultural display order is first name followed by last name. Real applications need to consider missing parts, titles, suffixes, localization, and user preferences.

Combining Text and Columns

Fixed text can label a column value for a report or export.

SELECT CONCAT('Employee: ', first_name) AS employee_label
FROM employee;

A label embedded in SQL can be useful for a fixed technical export. User-facing text is often better handled by the application so it can be translated and changed without modifying SQL.

CONCAT with Numbers

Many CONCAT implementations convert numeric arguments to text automatically. That behavior is convenient but depends on database rules for scale, scientific notation, decimal separators, and output length.

SELECT CONCAT('Order #', order_id) AS order_label
FROM orders;

Use an explicit CAST or formatting function when the textual representation is part of a stable contract. Keep the original numeric value available for sorting and arithmetic.

Explicit CAST with CONCAT

Explicit conversion documents the expected type and can prevent product-specific implicit conversion from surprising consumers.

SELECT CONCAT(
         'Order #',
         CAST(order_id AS VARCHAR(20))
       ) AS order_label
FROM orders;

The target length must accommodate the largest valid value. Truncation or overflow behavior differs among conversion functions and settings.

Adding Separators

Spaces, commas, hyphens, slashes, colons, pipes, and line breaks must be supplied explicitly. A separator should make the result unambiguous and match the output contract.

SELECT CONCAT(country_code, '-', customer_number) AS customer_reference
FROM customer;

A hyphen prevents values such as country 1/customer 23 and country 12/customer 3 from both becoming 123. Even with a separator, escaping rules matter when component values can contain that same character.

Creating Full Names

Names are structured data and vary across cultures. A simple CONCAT is suitable only when the system's display rule is known.

SELECT employee_id,
       TRIM(
         CONCAT(
           COALESCE(first_name, ''),
           ' ',
           COALESCE(last_name, '')
         )
       ) AS full_name
FROM employee;

This avoids a NULL result in products where needed and removes outer spaces. It still does not solve middle names, suffixes, mononyms, or locale-specific order. Preserve separate name fields.

Creating Addresses

CONCAT can create a one-line address for a specific report, but addresses are highly regional and often contain optional components.

SELECT CONCAT(
         street_line1, ', ',
         city, ', ',
         region_code, ' ',
         postal_code
       ) AS address_line
FROM customer_address;

A fixed punctuation pattern can produce doubled commas when fields are missing and can be wrong for international addresses. Use country-aware formatting in the appropriate presentation layer when necessary.

CONCAT and NULL

NULL behavior is database-specific. Some CONCAT functions treat NULL as an empty string, while an operator may propagate NULL. Other products and settings follow different rules.

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

Do not assume the result when either name is NULL. Test the target DBMS and make the desired fallback visible with COALESCE when portability or correctness requires it.

Safe NULL Handling

COALESCE can replace a missing component with an empty string or a meaningful label. Placement affects separators and output meaning.

SELECT TRIM(
         CONCAT(
           COALESCE(first_name, ''),
           ' ',
           COALESCE(last_name, '')
         )
       ) AS full_name
FROM employee;

If both names are NULL, this expression returns an empty string rather than NULL. That may be undesirable when no known name should remain distinguishable from a deliberately blank display value.

Optional Middle Names

An optional middle name illustrates why separators need conditional logic. Adding spaces unconditionally can create repeated spaces.

SELECT CONCAT(
         first_name,
         CASE
           WHEN middle_name IS NULL OR TRIM(middle_name) = '' THEN ''
           ELSE CONCAT(' ', TRIM(middle_name))
         END,
         ' ',
         last_name
       ) AS full_name
FROM employee;

For complex name rules, formatting in the application may be clearer and easier to localize.

CONCAT_WS

CONCAT_WS means concatenate with separator in databases that support it. The separator is supplied once and applied between subsequent values.

SELECT CONCAT_WS(
         ', ',
         street_line1,
         city,
         region_code,
         postal_code
       ) AS address_line
FROM customer_address;

CONCAT_WS often skips NULL arguments, but empty strings may still create separators. Verify exact behavior and availability in the target product.

CONCAT vs CONCAT_WS

ConcernCONCATCONCAT_WS
SeparatorRepeated as explicit argumentsDeclared once
Best useCustom text layoutConsistent separator-based lists
NULL behaviorProduct-specificOften skips NULL values
Empty stringsJoined as suppliedMay still produce separators

Use the function whose semantics match the required output rather than choosing only for shorter syntax.

CONCAT with TRIM

TRIM can remove leading and trailing spaces from source components or from the final value.

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

Query-time trimming can hide persistent dirty data. If surrounding spaces are always invalid, normalize them during ingestion and enforce an appropriate quality rule.

CONCAT with UPPER and LOWER

Case functions can prepare display labels or normalized search text.

SELECT UPPER(
         CONCAT(country_code, '-', customer_number)
       ) AS customer_reference,
       LOWER(TRIM(email)) AS normalized_email
FROM customer;

Case conversion follows collation and language rules. Do not assume that display casing and identity normalization are the same operation.

CONCAT with Dates

A date can be concatenated through implicit or explicit conversion, but the default textual format can depend on the database and session.

SELECT CONCAT(
         'Created: ',
         CAST(created_date AS VARCHAR(20))
       ) AS created_label
FROM orders;

For a stable export, choose an explicit unambiguous date representation. For localized display, pass the typed date to the application instead of embedding a language-specific string in SQL.

Creating Product Descriptions

CONCAT can build a compact product label from structured attributes.

SELECT product_id,
       CONCAT(brand_name, ' ', model_name, ' - ', color_name)
         AS product_label
FROM product;

Continue storing brand, model, and color separately so users can filter, validate, sort, translate, and update each attribute independently.

Creating Codes and References

Concatenated references can be useful for display, but they should not automatically become database keys.

SELECT CONCAT(tenant_code, '-', order_number) AS order_reference
FROM orders;

Define component lengths, separator escaping, case, NULL rules, and uniqueness. A composite primary or unique key preserves structure and avoids ambiguous parsing.

Ambiguous Concatenated Values

Joining values without separators can create collisions. The pairs (1, 23) and (12, 3) both become 123. Delimiters reduce this risk but do not solve it if values can contain the delimiter.

For hashing, signatures, deduplication, or identity, use a canonical encoding with length prefixes, escaping, typed serialization, or a real composite key rather than casual CONCAT output.

Keep Structured Data Structured

Do not store full names, addresses, or composite labels when the system still needs the individual components. Concatenated storage makes validation, updates, search, sorting, and localization harder.

Store atomic data according to the model and derive presentation text at the appropriate boundary. Persist a combined value only when it represents a genuine independent business attribute or a justified indexed/generated expression.

CONCAT in WHERE

A concatenated predicate is often non-sargable and can prevent indexes on the individual columns from being used efficiently.

-- Potentially expensive
WHERE CONCAT(first_name, ' ', last_name) = :full_name

-- Prefer structured predicates when inputs are available
WHERE first_name = :first_name
  AND last_name = :last_name

Structured predicates also avoid ambiguity and make NULL behavior visible.

CONCAT and Index Performance

The engine may compute CONCAT for every qualifying row before it can compare or sort the result. Expression indexes or generated columns can support a stable deterministic concatenation in products that allow them.

Measure read improvement against additional storage and write cost. Confirm that query expressions, collation, separators, and NULL behavior match the indexed definition exactly.

CONCAT in ORDER BY

Sorting by a concatenated display value can require computation and a full sort. It can also produce a different order from sorting by the underlying columns.

SELECT customer_id, first_name, last_name
FROM customer
ORDER BY last_name, first_name, customer_id;

Structured ordering is usually clearer and provides a stable tie breaker. Concatenate only for the selected display value.

CONCAT in GROUP BY

Grouping by concatenated values can merge distinct component combinations when the encoding is ambiguous or normalization collapses values.

Group by the real columns and concatenate in SELECT when a label is needed. This preserves grain and lets the database use column statistics more accurately.

CONCAT and Empty Strings

An empty string is different from NULL in most databases, although Oracle SQL generally treats zero-length character strings as NULL. CONCAT_WS may skip NULL but not empty strings, creating repeated separators.

Normalize whitespace and empty values according to the domain before concatenation. Do not assume that a visually blank field has one universal database representation.

CONCAT vs Concatenation Operators

SQL products also provide operators. PostgreSQL and Oracle commonly use ||, while SQL Server commonly uses +. MySQL can interpret || differently depending on SQL mode.

Operator NULL handling can differ from CONCAT. Choose one style according to product standards and portability requirements, then test NULL, types, and lengths.

MySQL CONCAT

MySQL CONCAT accepts multiple arguments and returns NULL if any argument is NULL. CONCAT_WS uses one separator and skips NULL values after the separator argument.

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

Use COALESCE or CONCAT_WS when nullable components require different behavior. Check character sets and collations when arguments differ.

SQL Server CONCAT

SQL Server CONCAT accepts multiple values and implicitly converts arguments to strings, treating NULL as an empty string. The plus operator follows different conversion and NULL rules depending on types and settings.

Explicit conversion remains useful when output length, numeric scale, or date format is part of a contract. CONCAT_WS is available in supported modern versions.

PostgreSQL CONCAT

PostgreSQL provides CONCAT and CONCAT_WS in addition to the standard-looking || operator. CONCAT generally ignores NULL arguments, while the operator propagates NULL.

This difference is important during migrations. A query rewritten from CONCAT to || can change output when nullable data exists.

Oracle Concatenation

Oracle supports CONCAT with two arguments and commonly uses the || operator for several values. Oracle's treatment of empty character strings as NULL also affects portability.

Nested CONCAT calls are possible, but the operator is often clearer. Test target data types, character semantics, and maximum output size.

SQLite Concatenation

SQLite traditionally uses the || operator for concatenation, and function availability can depend on version or extensions. The operator returns NULL when an operand is NULL.

Applications that support SQLite and server databases should keep concatenation differences behind tested query adapters or use compatible syntax deliberately.

Output Types and Length Limits

The return type and maximum length can depend on argument types and database rules. Combining large text, binary-like values, or many columns may produce a large object type or trigger truncation in an intermediate conversion.

Test the longest valid result, multibyte characters, and client-driver mapping. Do not assume that a successful short example proves large production output is safe.

Unicode and Collation

Concatenating values with different character sets or collations may require conversion or cause an error. Case, sorting, equality, and length can vary under Unicode and locale rules.

Use compatible Unicode types and a documented collation strategy. Count characters rather than bytes when validating user-visible limits, unless the external protocol defines a byte limit.

Localization and Presentation

Labels, date text, address order, and name order vary by language and region. Hardcoded English words and punctuation inside SQL are difficult to translate.

Return structured typed values to the application for user-facing localization. Reserve SQL concatenation for stable technical formats, controlled exports, or database-owned reporting conventions.

CONCAT and SQL Injection

CONCAT in a normal SELECT expression does not itself execute the resulting text. The security risk appears when concatenated values are used to construct dynamic SQL and then executed.

Use parameterized SQL, allowlisted identifiers, and product-specific safe dynamic-SQL APIs. Never concatenate untrusted input into executable SQL, even after replacing quotes manually.

Generated Columns and Expression Indexes

A frequently used stable concatenated reference can be represented by a generated column or expression index when product rules allow it. This can improve lookup and enforce a normalized unique representation.

The expression becomes schema logic. Changes to separators, types, collation, or component length require migration, index rebuild, collision validation, and rollback planning.

Real-World Customer Example

A customer export can include a readable label while retaining separate fields.

SELECT customer_id,
       CONCAT(
         COALESCE(display_name, legal_name, 'Unknown customer'),
         ' (',
         customer_number,
         ')'
       ) AS customer_label
FROM customer;

Use the typed customer_id or customer_number for joins and links, not the display label.

Real-World Order Label

An operations screen may need a compact order label.

SELECT order_id,
       CONCAT(
         tenant_code,
         '-',
         order_number,
         ' / ',
         status
       ) AS order_label
FROM orders;

Status remains a separate column for filtering and localization. The label is presentation output, not the authoritative order identity.

CONCAT Is Not String Aggregation

CONCAT combines expressions from one row. It does not automatically combine values from several rows. Building a comma-separated list across rows requires a string aggregate such as STRING_AGG, GROUP_CONCAT, LISTAGG, or another product-specific function.

-- Product-specific aggregate example
SELECT customer_id,
       STRING_AGG(product_name, ', ') AS purchased_products
FROM customer_product
GROUP BY customer_id;

List aggregates need explicit ordering, NULL rules, duplicate rules, and output-size limits. Do not confuse them with scalar CONCAT.

CONCAT for CSV and Delimited Exports

Joining values with commas does not create a safe CSV record. Values can contain commas, quotes, line breaks, and formula-like text. A correct CSV writer must quote and escape according to the format specification.

Prefer the database export tool, application CSV library, or ETL platform. If SQL must produce delimited output, define encoding, NULL representation, quoting, line endings, column order, and spreadsheet-injection protection, then test troublesome values.

Building URLs and File Paths

CONCAT can assemble a URL or storage path, but raw values may contain reserved characters, path separators, Unicode, traversal sequences, or spaces that require encoding.

SELECT CONCAT('/orders/', CAST(order_id AS VARCHAR(20))) AS relative_path
FROM orders;

This is safe only because the numeric identifier has a constrained representation. Use a URL encoder or path library for arbitrary text, and never concatenate untrusted path segments without validation.

Building Email Addresses

An organization may derive an email candidate from a username and domain, but CONCAT alone does not validate syntax, uniqueness, internationalization, or ownership.

SELECT CONCAT(normalized_username, '@', email_domain) AS email_candidate
FROM account_provisioning;

Enforce uniqueness with a database constraint on the normalized business key and use a dedicated validation process. Preserve the actual confirmed address rather than recalculating it after a username changes.

Composite Identifiers and Length Prefixes

When a reversible combined representation is unavoidable, plain separators may be insufficient because component values can contain the separator. A length-prefixed encoding records the size of each part before its content.

SELECT CONCAT(
         CHAR_LENGTH(part_a), ':', part_a,
         CHAR_LENGTH(part_b), ':', part_b
       ) AS encoded_pair
FROM source_data;

This is still text encoding rather than a relational key. Use structured columns for joins and constraints whenever possible.

CONCAT Before Hashing

Data pipelines sometimes concatenate columns and hash the result for change detection. Ambiguous concatenation, NULL handling, collation, case, and formatting can make different rows produce the same pre-hash text or the same row produce different hashes.

Define a canonical serialization with column order, types, lengths, encoding, NULL markers, time-zone normalization, and numeric precision. A cryptographic hash does not repair an ambiguous input representation.

ETL and Data-Warehouse Usage

ETL processes use CONCAT for business-key staging, audit labels, partition names, and source-system references. These formats should be documented and versioned because downstream jobs may parse them.

Keep source attributes in separate staging columns even when a combined value is emitted. This supports reconciliation, rejection handling, backfills, and future format changes without reparsing old labels.

Concatenating Long Text

Combining descriptions, logs, JSON fragments, or document content can exceed ordinary character limits. The database may promote the result to a large-object type, truncate an intermediate expression, spill memory, or transfer a large payload to the client.

Test maximum production sizes and driver behavior. Stream or aggregate large content with supported tools rather than constructing one unbounded value per group or request.

Line Breaks and Control Characters

Multi-line labels require product- and platform-specific newline handling. A character function, escaped literal, or explicit line-break sequence may be needed. Windows and Unix consumers can expect different line endings.

Control characters can break logs, CSV files, terminals, and APIs. Validate output destinations and escape or remove disallowed characters using a documented policy rather than a broad replacement that damages legitimate Unicode text.

Generated Display Columns

A generated column can expose a stable concatenated label, but display conventions change more often than identity rules. Persisting a localized or presentation-focused label in the schema can create unnecessary migrations.

Use generated concatenation when it supports a stable technical requirement such as a normalized reference or expression index. Keep flexible user-facing text in the application or reporting layer.

Auditing Concatenated Output

Audit messages often concatenate actor, action, entity, and timestamp. A human-readable message is useful, but it should not be the only audit representation because labels can change and text is difficult to query reliably.

Store structured actor ID, action code, entity ID, timestamp, tenant, and metadata alongside any rendered message. Reports can regenerate localized display text while the structured event remains authoritative.

Performance with Large Result Sets

CONCAT is usually inexpensive per row, but cost scales with row count, argument length, conversion work, collation, and output transfer. A query that concatenates several large fields for millions of rows can consume substantial CPU, memory, network bandwidth, and client processing.

Select only needed rows and columns, paginate deterministically, and avoid building labels that the consumer never displays. Measure server and network cost together because reducing database CPU can still increase payload size.

Monitoring and Regression Testing

Monitor queries that use concatenated search, sort, or join expressions for plan changes, scans, high CPU, spills, and increased output size. Data growth or a new nullable component can change behavior without changing SQL text.

Keep sample outputs and edge-case tests for stable exports and integrations. A separator, NULL, date-format, or collation change can break downstream parsing even when the query continues to succeed.

Deterministic Exports and Pagination

A concatenated label does not provide deterministic row order. Exports and paginated queries need an explicit ORDER BY with a unique tie breaker so repeated executions do not move equal labels between pages.

SELECT order_id,
       CONCAT(tenant_code, '-', order_number) AS order_reference
FROM orders
ORDER BY tenant_code, order_number, order_id;

Sort on structured values whenever they define the intended order. Formatting, collation, case conversion, and NULL fallback can change the order of a concatenated string. Stable ordering is especially important for incremental exports, resume tokens, and batch reconciliation.

Sensitive Data and Concatenated Labels

Combining customer name, email, account number, or address can create a new sensitive value even when each individual column is protected separately. Review data classification, authorization, logging, caching, and export permissions for the final label.

Do not concatenate secrets into error messages or audit text. Masking a portion of an account number is a display control, not authorization or encryption. Apply row and tenant access before concatenation, and return only the minimum data required by the consumer.

Testing CONCAT Expressions

Test all components present, each component NULL, all components NULL, empty strings, whitespace-only values, separators inside data, Unicode, maximum lengths, numeric boundaries, date values, and incompatible collations.

Portability tests should compare CONCAT, CONCAT_WS, and operators on every supported DBMS. Performance tests should compare concatenated predicates with structured indexed predicates using realistic volume.

Common Mistakes

  • Expecting CONCAT to insert spaces or punctuation automatically.
  • Ignoring database-specific NULL and empty-string behavior.
  • Relying on implicit numeric or date formatting for stable output.
  • Searching or joining on concatenated columns without measuring index impact.
  • Creating ambiguous values without separators or escaping.
  • Using display labels as database keys.
  • Storing combined names or addresses instead of structured components.
  • Hardcoding localized labels and address order in shared SQL.
  • Ignoring output length, Unicode, and collation.
  • Building executable SQL by concatenating untrusted input.

Best-Practice Checklist

  1. Add every required separator explicitly.
  2. Define NULL and empty-string behavior.
  3. Use COALESCE only with meaningful fallbacks.
  4. Use explicit CAST for stable numeric and date representations.
  5. Keep names, addresses, keys, dates, and numbers structured.
  6. Use CONCAT_WS when separator semantics match the requirement.
  7. Avoid concatenated predicates and joins when structured comparisons work.
  8. Define Unicode, collation, output length, and localization rules.
  9. Use parameterized SQL for dynamic statements.
  10. Test edge cases and every supported database product.

Interview Questions and Answers

What does CONCAT do? It joins two or more values into one string.

Does CONCAT add spaces automatically? No. Spaces and punctuation must be supplied as arguments.

Can CONCAT combine columns and literals? Yes. It can combine table values, fixed text, and compatible expressions.

Can CONCAT combine numbers? Many databases convert numbers implicitly, but explicit CAST gives better control.

How does CONCAT handle NULL? Behavior differs by DBMS. Some propagate NULL, while others treat it as empty or skip it.

What is CONCAT_WS? It concatenates values using one declared separator and often skips NULL arguments.

Can CONCAT be nested? Yes, though CONCAT_WS, CASE, or clearer staged logic may be easier to read.

Why avoid CONCAT in WHERE? It can make predicates non-sargable, create ambiguity, and prevent efficient use of indexes on component columns.

Is CONCAT the same as a concatenation operator? Not always. NULL, type conversion, and portability can differ.

Key Takeaway

CONCAT creates useful display text by joining values in a defined order. Reliable use requires explicit separators, deliberate NULL handling, controlled conversion, database-specific testing, and a clear boundary between structured data and presentation output.

RELIABLE CONCATENATION = EXPLICIT COMPONENT ORDER
                       + UNAMBIGUOUS SEPARATORS
                       + DEFINED NULL AND EMPTY RULES
                       + CONTROLLED TYPE CONVERSION
                       + STRUCTURED STORAGE AND SEARCH
                       + PORTABILITY AND EDGE-CASE TESTING

Use CONCAT for labels and controlled text output, not as a shortcut for data modeling, keys, filtering, or dynamic SQL security.