SUBSTRING
 SUBSTRING ( <source-string> FROM <start-position> [ FOR <string-length> ] ) SUBSTRING ( <source-string>, <start-position> [ , <string-length> ] ) SUBSTRING ( <source-string> SIMILAR <pattern> ESCAPE <escape-char> ) <source-string> := <character-expression> <start-position> := <integer-expression> <string-length> := <integer-expression> <regex-expression> := <character-expression> <pattern> := <character-expression> <escape-char> := <character-expression>
SUBSTRING extracts a portion of the source string specified in the first argument. Extraction starts at the value of start-position or the first expression matching the value of regex-expression.
If a value is specified for string-length, only that number of characters is 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, the string length defaults to the remaining length of the input string.
If the start position is less than 1, then the start position is interpreted as if it is 1 and the string length is reduced by (1–start position). For examples, see following. 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.
Parameters
source-string
The string to search for positional or regular-expression matches.
start-position
The first character of source-string to return. If start-position is greater than the length of source-string, SUBSTRING returns null.
string-length
The number of characters from source-string to return.
regex-expression
A pattern of characters to match and return from source-string. Only the first match is returned.
pattern
A three-part pattern of characters that consists of the following:
The string to be found before the returned substring
The returned substring
The string to be found after the returned substring
The parts are delimited by a double quotation mark (") and a specified escape character. For more information, see Similar...Escape Samples following.
Examples
FROM/ FOR
| Function | Result |
|---|---|
|
SUBSTRING('123456789' FROM 3 FOR 4) |
3456 |
|
SUBSTRING('123456789' FROM 17 FOR 4) |
<empty string> |
|
SUBSTRING('123456789' FROM -1 FOR 4) |
12 |
|
SUBSTRING('123456789' FROM 6 FOR 0) |
<empty string> |
|
SUBSTRING('123456789' FROM 8 FOR 4) |
89 |
FROM Regex
| Function | Result |
|---|---|
SUBSTRING('TECHNOLOGY' FROM 'L[A-Z]*') | LOGY |
SUBSTRING('TECHNOLOGY' FROM 'FOO') | null |
SUBSTRING('TECHNOLOGY' FROM 'O[A-Z]') | OL |
Numeric
| Function | Result |
|---|---|
|
SUBSTRING('123456789', 3, 4) |
3456 |
|
SUBSTRING('123456789', 7, 4) |
789 |
|
SUBSTRING('123456789', 10, 4) |
null |
Similar...Escape
| Function | Result |
|---|---|
|
SUBSTRING('123456789' SIMILAR '23#"456#"78' ESCAPE '#') |
456 |
|
SUBSTRING('TECHNOLOGY' SIMILAR 'TECH%"NOLO%"GY' ESCAPE '%') |
NOLO |
Notes
Amazon Kinesis Data Analytics streaming SQL doesn't support the optional 'USING CHARACTERS | OCTETS' clause defined in SQL:2008. USING CHARACTERS is simply assumed.
The second and third forms of the SUBSTRING function listed preceding (using a regular expression, and using commas rather than FROM...FOR) are not part of the SQL:2008 standard. They are part of the streaming SQL extension to Amazon Kinesis Data Analytics.
