MaxCompute:SUBSTR

start_position behavior

• Positive value: Counting starts from the beginning of the string; position 1 is the first character.

• Zero (`0`):

  • In data type edition 1.0 and 2.0: returns an empty string.

  • In the Hive compatible data type edition: starts from the first character.

  • In BigQuery compatible mode (SET odps.sql.bigquery.compatible=true;): starts from the first character, same as 1.

• Negative value: Counting starts from the end of the string. For example, -1 refers to the last character.

  • If the absolute value exceeds the string length (that is, |start_position| > length(str)): returns an empty string.

  • In BigQuery compatible mode: starts from the first character instead of returning an empty string.

length behavior

• length = 0: returns an empty string.

• length < 0: returns an empty string. In BigQuery compatible mode (SETPROJECT odps.sql.bigquery.compatible=true;), an error is returned instead.

Basic usage

-- Returns 'bc' (starts at position 2, to end of string)
SELECT SUBSTR('abc', 2);

-- Returns 'b' (starts at position 2, length 1)
SELECT SUBSTR('abc', 2, 1);

-- Returns 'bc' (starts 2 characters from the end, length 2)
SELECT SUBSTR('abc', -2, 2);

NULL input

-- Returns NULL
SELECT SUBSTR('abc', null);

BigQuery compatible mode

The following examples require BigQuery compatible mode. Enable it with SET odps.sql.bigquery.compatible=TRUE; before running each example.

start_position = 0 is treated as 1:

SET odps.sql.bigquery.compatible=TRUE;

-- Returns 'apple' (same as start_position = 1)
SELECT SUBSTR('apple', 0);

start_position out of range (less than -length) starts from position 1:

SET odps.sql.bigquery.compatible=TRUE;

-- Returns 'apple' ('apple' has 5 characters, so -6 is out of range)
SELECT SUBSTR('apple', -6);

Negative length returns an error:

SET odps.sql.bigquery.compatible=TRUE;

-- Returns an error:
-- FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed:
-- SQL Runtime Unretryable Error: ODPS-0121095:Invalid argument - in function substr/substring,
-- unsupported length value -1
SELECT SUBSTR('apple', 1, -1);