Return a substring from a string with the SUBSTRING function - MaxCompute

Syntax

string substring(string|binary <str>, int <start_position>[, int <length>])
string substr(string|binary <str>, int <start_position>[, int <length>])

Parameters

str: Required. A STRING or BINARY value. Implicit conversion from compatible types is supported.

start_position: Required. An INT value that specifies where the substring begins. The index is 1-based — position 1 is the first character.

Value	Behavior	Example
Positive integer	Counts from the start of the string.	`substring('abc', 2)` → `bc`
`0`	Returns an empty string.	`substring('abc', 0)` → ``
Negative integer	Counts from the end of the string. `-1` is the last character.	`substring('abc', -2, 2)` → `bc`

length: Optional. A BIGINT value that specifies the number of characters to return. Must be greater than 0. If omitted, the function returns all characters from start_position to the end of str.

Return value

Returns a STRING value.

Condition	Return value
`str` is not STRING or BINARY and cannot be implicitly converted	Error
`length` is not BIGINT or is less than or equal to `0`	Error
`length` is omitted	Substring from `start_position` to the end of `str`
`str`, `start_position`, or `length` is `null`	`null`

Examples

Basic usage

-- Returns bc (starts at position 2, no length limit)
select substring('abc', 2);

-- Returns b (1 character starting at position 2)
select substring('abc', 2, 1);

-- Returns bc (-2 is the second-to-last character; extracts 2 characters)
select substring('abc', -2, 2);

-- Returns ab (-3 is the first character; extracts 2 characters)
select substring('abc', -3, 2);

-- Returns 001 (bin(2345) produces a binary representation; extracts characters 2-4)
select substring(bin(2345), 2, 3);

Null input

-- Returns null (any null argument causes null output)
select substring('abc', null, null);

Boundary value reference

The following table shows how different start_position and length values affect the result for the input 'maxcompute' (10 characters):

`start_position`	`length`	Result	Notes
`1`	`3`	`max`	Starts at the first character
`4`	`7`	`compute`	Starts at position 4
`-7`	`7`	`compute`	`-7` = position 4 from the start
`1`	omitted	`maxcompute`	No `length` returns the rest of the string
`0`	`3`	``	Position `0` always returns an empty string
`1`	`null`	`null`	Any `null` argument returns `null`

Common patterns

-- Parse year, month, and day from a date string in YYYYMMDD format
select
  substring(date_str, 1, 4) as year,
  substring(date_str, 5, 2) as month,
  substring(date_str, 7, 2) as day
from events;

-- Extract the first 3 characters as a category code
select substring(product_code, 1, 3) as category from products;

-- Extract a fixed-length region code from a location identifier
select substring(location_id, 4, 2) as region_code from stores;

What's next

SUBSTRING is a string function. For related string search and conversion functions, see String functions.