<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > Basic Building Blocks > Functions > Scalar Functions >


Previous pageReturn to chapter overviewNext page

 SUBSTRING ( <source-string> FROM <start-position> [ FOR <string-length> ] )

SUBSTRING ( <source-string>, <start-position> [ , <string-length> ] )

<source-string> := <character-expression>

<start-position> := <integer-expression>

<string-length> := <integer-expression>


SUBSTRING extracts a portion of the source-string specified in the first argument, starting at start-position.

If string-length is specified, only string-length characters are returned (if there aren't that many characters left in the string, only the characters that are left are returned). If string-length is not specified, it defaults to the remaining length of the input string.

If the start position is less than 1, then it is interpreted as if the start position is 1 and the string length is reduced by (1 - start position). See examples below. If the start position is greater than the number of characters in the string, or the length parameter is 0, the result is an empty string.




SUBSTRING('123456789' FROM 3 FOR 4)


SUBSTRING('123456789', 3, 4)


SUBSTRING('123456789' FROM -1 FOR 4)


SUBSTRING('123456789' FROM 8 FOR 4)


SUBSTRING('123456789' FROM 17 FOR 4)

<empty string>

SUBSTRING('123456789' FROM 6 FOR 0)

<empty string>


SQLstream streaming SQL does not support the regular expression form of SUBSTRING defined in SQL:2008 (i.e., 'SUBSTRING ... SIMILAR ... ESCAPE').
Additionally, SQLstream streaming SQL does not support the optional 'USING CHARACTERS | OCTETS' clause defined in SQL:2008; USING CHARACTERS is simply assumed.
Finally, the second form of the SUBSTRING function listed above (using commas rather than FROM...FOR) is not part of the SQL:2008 standard; it is a SQLstream streaming SQL extension.