SUBSTRING

​

Syntax

​

To end (scalar)

substring(value, position)

​

Scalar

substring(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 substring('abcdef', 2, 3) AS result;

​

Omitted length

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

​

Negative and zero positions

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

​

Empty results

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

​

NULL input

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

​

Binary positions count bytes

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

​

Non-integer position is rejected

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

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

​

MID rejects a non-integer position

• MID requires integer position and length arguments. This call is rejected.

SELECT mid('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