SUBSTR

Returns VARCHAR or VARBINARY value representing a substring of a specified string.

Behavior Type

Immutable

Syntax

SUBSTR ( string , position [ , extent ] )

Parameters

string

(CHAR/VARCHAR or BINARY/VARBINARY) is the string from which to extract a substring. If null, Vertica returns no results.

 
position

(INTEGER or DOUBLE PRECISION) is the starting position of the substring (counting from one by characters). If 0 or negative, Vertica returns no results.

 
extent

(INTEGER or DOUBLE PRECISION) is the length of the substring to extract (in characters). The default is the end of the string.

Notes

SUBSTR truncates DOUBLE PRECISION input values.

Examples

=> SELECT SUBSTR('abc'::binary(3),1);
 substr
--------
 abc
(1 row)
=> SELECT SUBSTR('123456789', 3, 2);
 substr
--------
 34
(1 row)
=> SELECT SUBSTR('123456789', 3);
 substr
---------
 3456789
(1 row)
=> SELECT SUBSTR(TO_BITSTRING(HEX_TO_BINARY('0x10')), 2, 2);
 substr 
--------
 00
(1 row)
=> SELECT SUBSTR(TO_HEX(10010), 2, 2);
 substr 
--------
 71
(1 row)