String Functions
Amazon S3 Select and S3 Glacier Select support the following string functions.
CHAR_LENGTH, CHARACTER_LENGTH
Counts the number of characters in the specified string.
CHAR_LENGTH
and CHARACTER_LENGTH
are
synonyms.
Syntax
CHAR_LENGTH ( string )
Parameters
- string
-
The target string that the function operates on.
Examples
CHAR_LENGTH('') -- 0 CHAR_LENGTH('abcdefg') -- 7
LOWER
Given a string, converts all uppercase characters to lowercase characters. Any non-uppercased characters remain unchanged.
Syntax
LOWER ( string )
Parameters
- string
-
The target string that the function operates on.
Examples
LOWER('AbCdEfG!@#$') -- 'abcdefg!@#$'
SUBSTRING
Given a string, a start index, and optionally a length, returns the substring from the start index up to the end of the string, or up to the length provided.
The first character of the input string has index 1.
-
If
start
is < 1, with no length specified then it is set to 1. -
If
start
is < 1, with length specified then it is set tostart + length -1
. -
If
start + length -1
< 0 then an empty string is returned. -
If
start + length -1
> = 0 then the sub-string starting at index 1 with lengthstart + length - 1
is returned.
Syntax
SUBSTRING( string FROM start [ FOR length ] )
Parameters
- string
-
The target string that the function operates on.
- start
-
The start position of the string.
- length
-
The length of the substring to return. If not present, proceed to the end of the string.
Examples
SUBSTRING("123456789", 0) -- "123456789" SUBSTRING("123456789", 1) -- "123456789" SUBSTRING("123456789", 2) -- "23456789" SUBSTRING("123456789", -4) -- "123456789" SUBSTRING("123456789", 0, 999) -- "123456789" SUBSTRING("123456789", 1, 5) -- "12345"
TRIM
Trims leading or trailing characters from a string. The default character to remove is ' '.
Syntax
TRIM ( [[LEADING | TRAILING | BOTH remove_chars] FROM] string )
Parameters
- string
-
The target string that the function operates on.
- LEADING | TRAILING | BOTH
-
Whether to trim leading or trailing characters, or both leading and trailing characters.
- remove_chars
-
The set of characters to remove. Note that
remove_chars
can be a string with length > 1. This function returns the string with any character fromremove_chars
found at the beginning or end of the string that was removed.
Examples
TRIM(' foobar ') -- 'foobar' TRIM(' \tfoobar\t ') -- '\tfoobar\t' TRIM(LEADING FROM ' foobar ') -- 'foobar ' TRIM(TRAILING FROM ' foobar ') -- ' foobar' TRIM(BOTH FROM ' foobar ') -- 'foobar' TRIM(BOTH '12' FROM '1112211foobar22211122') -- 'foobar'
UPPER
Given a string, converts all lowercase characters to uppercase characters. Any non-lowercased characters remain unchanged.
Syntax
UPPER ( string )
Parameters
- string
-
The target string that the function operates on.
Examples
UPPER('AbCdEfG!@#$') -- 'ABCDEFG!@#$'