Skip to main content

Concatenation Operator (||)

The concatenation operator || is used to join two or more string values together into a single string. It is the standard SQL operator for string concatenation.

Syntax

string1 || string2 [ || string3 ... ]

Arguments

  • string1, string2, etc.: String expressions to be concatenated.

Returns

  • Returns a VARCHAR containing the concatenated result of all input strings.

  • If any operand is NULL, the result is NULL.

Limits

  • All operands must be string types or implicitly convertible to strings.

  • The result length is the sum of the lengths of all input strings.

Examples

Basic Concatenation

-- Example 1: Concatenate two strings
> SELECT 'Hello' || ' ' || 'World';
'Hello World'

-- Example 2: Concatenate column values
> SELECT first_name || ' ' || last_name AS full_name
  FROM employees;

-- Example 3: Concatenate multiple strings
> SELECT 'Order #' || order_id || ' - ' || status AS order_info
  FROM orders;

NULL Handling

-- Example 4: NULL in concatenation returns NULL
> SELECT 'Hello' || NULL || 'World';
NULL

-- Example 5: Use COALESCE to handle NULLs
> SELECT first_name || ' ' || COALESCE(middle_name, '') || ' ' || last_name AS full_name
  FROM employees;

Type Conversion

-- Example 6: Concatenate with numbers (requires explicit cast)
> SELECT 'Total: ' || CAST(amount AS VARCHAR) AS total_text
  FROM invoices;

See Also