MID

​

Syntax

​

Scalar

mid(value, position, length)

​

Arguments

​

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.
• MID requires all three arguments, including length. To read through the end without a length argument, use SUBSTRING.

​

Examples

​

Substring with a length limit

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

​

Negative and zero positions

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

​

Empty results

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

​

NULL input

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

​

Binary positions count bytes

SELECT hex(mid(X'616263646566', 2, 3)) 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