Guavus SQLstream supports three subtypes of the SUBSTRING function:
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>
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.
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> |
Extract a substring matching a regular expression.
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.
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.
VALUES (substring('SQLstream' from '...$'));
+-----------+
| EXPR$0 |
+-----------+
| eam |
+-----------+
This function extracts a substring matching a SQL regular expression between escape characters such as “#”.
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.
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.
values substring('SQLstream' from '%#"r_a#"_' for '#');
+-----------+
| EXPR$0 |
+-----------+
| rea |
+-----------+