SUBSTR syntax, parameters, and return value - MaxCompute

Returns a substring from string str, starting at start_position with a length of length.

Syntax

STRING SUBSTR(STRING <str>, BIGINT <start_position>[, BIGINT <length>])

Parameters

str: Required. A value of the STRING type. If the input value is of the BIGINT, DECIMAL, DOUBLE, or DATETIME type, it is implicitly converted to the STRING type before calculation.

start_position: Required. A value of the BIGINT type. The function's behavior varies based on the value of start_position:

start_position value	Description
1	The substring starts from the first character by default.
0	In MaxCompute data type edition 1.0 and data type edition 2.0, an empty string is returned. If you run the `SET odps.sql.bigquery.compatible=true;` command to enable the BigQuery compatible mode, the substring starts from the first character. In the MaxCompute Hive compatible data type edition, the substring starts from the first character by default.
A negative number	The substring starts from the last character by default. If the value of start_position is less than -length (where length is the length of the string str): An empty string is returned. If you run the `SET odps.sql.bigquery.compatible=true;` command to enable the BigQuery compatible mode, the substring starts from the first character.

length: Optional. A value of the BIGINT type. This parameter specifies the length of the substring. If you do not specify this parameter, the function returns the substring from the start position to the end of the str string.
- If the value of length is 0, an empty string is returned.
- If the value of length is less than 0, an empty string is returned. However, an error is returned if the following flag is enabled:
  - If you enable the BigQuery compatible mode by running the SETPROJECT odps.sql.bigquery.compatible=true; command, an error is returned.

Return value

The function returns a value of the STRING type. The following rules apply:

If the type of str is not STRING, BIGINT, DECIMAL, DOUBLE, or DATETIME, an error is returned.
If the type of length is not BIGINT, an error is returned.
If str, start_position, or length is NULL, the function returns NULL.

Examples

Example 1: Return a substring of a specified length from the string abc, starting at a specified position. Sample command:

-- bc is returned.
SELECT SUBSTR('abc', 2);

-- b is returned.
SELECT SUBSTR('abc', 2, 1);

-- bc is returned.
SELECT SUBSTR('abc', -2, 2);

Example 2: An input parameter is NULL. Sample command:
```
-- NULL is returned.
SELECT SUBSTR('abc', null);
```

Example 3: In BigQuery compatible mode, if the value of start_position is 0, the result is the same as when the value is 1.

-- apple is returned.
SET odps.sql.bigquery.compatible=TRUE;  
SELECT SUBSTR('apple', 0);

-- This is equivalent to the following statement:
SELECT SUBSTR('apple', 1);

Example 4: In BigQuery compatible mode, if the value of start_position is less than -length (where length is the length of the string str), the result is the same as when the start position is 1.
```
-- apple is returned.
SET odps.sql.bigquery.compatible=TRUE;
SELECT SUBSTR('apple', -6);

-- This is equivalent to the following statement:
SELECT SUBSTR('apple', 1);
```

Example 5: In BigQuery compatible mode, if the value of length is less than 0, an error is returned.

-- The following error is returned: 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
SET odps.sql.bigquery.compatible=TRUE;
SELECT SUBSTR('apple',1,-1);

Related functions

SUBSTR is a string function. For more information about string search and conversion functions, see String functions.