SUBSTR

Extracts a portion of the string from the expression expr, starting at the start position and limited to length (optional). SUBSTR calculates lengths using characters defined by the input character set.

Syntax

SUBSTR(expr, start[, length])

Arguments

expr: Sequence of characters of data type STRING.

---

start: INTEGER or another expression of any data type that can be implicitly converted to INTEGER, resolved to an integer. It specifies the offset at which the substring begins.

---

length (optional): INTEGER or another expression of any data type that can be implicitly converted to INTEGER, resolved to an integer.

Return Values

The return value is of data type STRING.

Usage Notes

• If start = 0, it is treated as 1.
• If start > 0, counting starts from the beginning of expr to find the first character.
• If start < 0, counting starts backward from the end of expr.
• If length is omitted, all characters up to the end of expr are returned.
• If length < 1, NULL is returned.
• DECIMAL values passed as arguments to SUBSTR are automatically converted to integers.

Examples

select substr('Gaudeamus igitur.', 11,  6  ) as result1, 
       substr('Gaudeamus igitur.', -6,  6  ) as result2, 
       substr('Gaudeamus igitur.', 11      ) as result3,
       substr('Gaudeamus igitur.', NULL    ) as result4,
       substr('Gaudeamus igitur.', 0       ) as result5, 
       substr('Gaudeamus igitur.', 0,   0.5) as result6;

+---------+---------+---------+---------+-------------------+---------+
| RESULT1 | RESULT2 | RESULT3 | RESULT4 | RESULT5           | RESULT6 |
+---------+---------+---------+---------+-------------------+---------+
| igitur  | gitur.  | igitur. | NULL    | Gaudeamus igitur. | NULL    |
+---------+---------+---------+---------+-------------------+---------+