SUBSTRING

<< Click to Display Table of Contents >>

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

SUBSTRING

Previous pageReturn to chapter overviewNext page

SQLstream supports three subtypes of the SUBSTRING function:

SUBSTRING for string length. This returns a string from a starting position for a number of characters defined as an integer.

SUBSTRING Regex. This returns a string based on a Java regular expression.

SUBSTRING with Escape. This returns a string based on a SQL regular expression.

SUBSTRING String Length

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

SUBSTRING ( <sourceString> FROM <startPosition> [ FOR <stringLength> ] )

SUBSTRING ( <sourceString>, <startPosition> [ , <stringLength> ] )

 

<sourceString> := <character-expression>

<startPosition> := <integer-expression>

<stringLength> := <integer-expression>

 

Notes

If an integer expression appears after FROM, s-Server treats it as a start position for a string length. If a character expression appears after FROM, s-Server treats it as a regular expression. See SUBSTRING Regex and SUBSTRING with Escape below.

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

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

Examples

Function

Result

SUBSTRING('123456789' FROM 3 FOR 4)

3456

SUBSTRING('123456789', 3, 4)

3456

SUBSTRING('123456789' FROM -1 FOR 4)

12

SUBSTRING('123456789' FROM 8 FOR 4)

89

SUBSTRING('123456789' FROM 17 FOR 4)

<empty string>

SUBSTRING('123456789' FROM 6 FOR 0)

<empty string>

Limitations

SQLstream streaming SQL does not support the optional 'USING CHARACTERS | OCTETS' clause; USING CHARACTERS is simply assumed.

The second form of the SUBSTRING function listed above (using commas rather than FROM...FOR) is a SQLstream streaming SQL extension.

SUBSTRING Regex

5_3_indicator Version 5.3 Feature

Extract a substring matching a regular expression.

Syntax

SUBSTRING(<sourceString> FROM <javaRegexPattern>)

<sourceString> := <character-expression>

<javaRegexPattern> := <character-expression> that is a Java Regex pattern.

 

Returns a string with the same character encoding as sourceString.

Notes

Extracts the substring matching the javaRegexPattern.

Raises an error if javaRegexPattern does not have the same character encoding as sourceString.

Returns NULL if sourceString or javaRegexPattern is null.

javaRegexPattern

Specifies the regular expression string to be used as search pattern, as defined in java.util.regex.pattern. The length of the pattern cannot exceed 65535 characters. Must have the same character encoding as sourceString.

Example

substring('SQLstream' from '...$')        

 

Result

eam

SUBSTRING with Escape

5_3_indicator Version 5.3 Feature

This function extracts a substring matching a SQL regular expression between escape characters such as "#".

Syntax

SUBSTRING(<sourceString> FROM <sqlRegularExpr> FOR <escapeCharacter>)

<sourceString> := <character-expression>

<sqlRegularExpr> := SQL regular expression

<escapeCharacter> := Escape Character (should appear exactly twice in <sourceString>)

 

Returns a string with the same character encoding as sourceString.

Notes

Raises an error if sqlRegularExpr or escapeCharacter do not have the same character encoding as sourceString.

Returns NULL if sourceString, sqlRegularExpr, or escapeCharacter is null.

Raises an exception if the length of escapeCharacter is not 1

Raises an exception if sqlRegularExpr does NOT contain EXACTLY two occurrences of the substring consisting of escapeCharacter followed by ". For example, if the escape character is #, then the sqlRegularExpr must be a concatenation of 3 sub-regex-expressions, where the middle expression is flanked by the substring #". For example, this would be a valid sqlRegularExpr: '%#"o_b#"%'

Raises an exception if the sqlRegularExpr does not decompose into 3 parts as described above.

Returns the portion of sourceString which matches the middle.

Example

values substring('SQLstream' from '%#"r_a#"_' for '#');        

 

Result:  

rea