SUBSTR

​

Syntax

​

To end (scalar)

substr(value, position)

​

Scalar

substr(value, position, length)

​

Arguments

​

To end

​

Scalar

​

Returns

• Type: STRING or BINARY
• NULL behavior: NULL arguments return NULL. Position 0 starts at the beginning. Nonpositive length returns an empty value. Strings count characters; binary values count bytes.

​

Properties

​

Units

• Input unit: Characters for STRING; bytes for BINARY.
• Output unit: Characters for STRING; bytes for BINARY.
• Omitting length reads through the end. Positions past the available input produce an empty value.

​

Usage notes

• Position and length are ordinary arguments. Neither is a pre-call configuration parameter or required to be a constant.
• The two-argument form has no length argument; it reads through the end instead of substituting a numeric default.

​

Examples

​

Substring with a length limit

SELECT substr('abcdef', 2, 3) AS result;

​

Omitted length

SELECT substr('abcdef', 3) AS result;

​

Negative and zero positions

SELECT substr('abcdef', -2, 2) AS negative_position, substr('abcdef', 0, 2) AS zero_position;

​

Empty results

SELECT substr('abcdef', 2, 0) AS zero_length, substr('abcdef', 2, -1) AS negative_length, substr('abcdef', 20, 2) AS past_end;

​

NULL input

SELECT substr(CAST(NULL AS VARCHAR), 2, 3) AS result;

​

Binary positions count bytes

SELECT hex(substr(X'616263646566', 2, 3)) AS result;

​

Non-integer position is rejected

• The explicit DOUBLE cast demonstrates the integer-argument restriction.

SELECT substr('abcdef', CAST(1.5 AS DOUBLE), 2) AS result;

​

Limitations

• Use integer positions and lengths. Other numeric types and STRING values are outside these calling forms.

​

Related functions

• left
• right
• length