String functions
ASCII
Syntax:ASCII( string )
Returns the numeric representation of the first character of the string.
CHAR
Syntax:CHAR( string )
Converts the numeric representation of an ASCII character to a value.
CONCAT
Syntax:CONCAT( arg_1, arg_2, arg_3 [ , ... ] )
Merges any number of strings. When non-string types are used, they're converted to strings and then merged.
CONTAINS
Syntax:CONTAINS( string, substring )
Returns TRUE
if string
contains substring
. For case-insensitive searches, see ICONTAINS.
ENDSWITH
Syntax:ENDSWITH( string, substring )
Returns TRUE
if string
ends in substring
. For case-insensitive searches, see IENDSWITH.
FIND
Syntax:FIND( string, substring [ , start_index ] )
Returns the index of the position of the first character of the substring substring
in the string string
.
If the start_index
option is specified, the search starts from the specified position.
ICONTAINS
Syntax:ICONTAINS( string, substring )
Case-insensitive version of CONTAINS. Returns TRUE
if string
contains substring
.
IENDSWITH
Syntax:IENDSWITH( string, substring )
Case-insensitive version of ENDSWITH. Returns TRUE
if string
ends in substring
.
ISTARTSWITH
Syntax:ISTARTSWITH( string, substring )
Case-insensitive version of STARTSWITH. Returns TRUE
if string
starts with substring
.
LEFT
Syntax:LEFT( string, number )
Returns a string that contains the number of characters specified in number
from the beginning of the string string
.
LEN
Syntax:LEN( value )
Returns the number of characters in the string or items in array value
.
LOWER
Syntax:LOWER( string )
Returns the string string
in lowercase.
LTRIM
Syntax:LTRIM( string )
Returns the string string
without spaces at the beginning of the string.
REGEXP_EXTRACT
Syntax:REGEXP_EXTRACT( string, pattern )
Returns the substring string
that matches the regular expression pattern
.
REGEXP_EXTRACT_ALL
Syntax:REGEXP_EXTRACT_ALL( string, pattern )
Returns all string
substrings matching the pattern
regex. For regexes with subgroups, it only works for the first subgroup.
REGEXP_EXTRACT_NTH
Syntax:REGEXP_EXTRACT_NTH( string, pattern, match_index )
Returns a substring string
that matches the regular expression pattern pattern
starting from the specified index.
REGEXP_MATCH
Syntax:REGEXP_MATCH( string, pattern )
Returns 'TRUE' if the string string
has a substring that matches the regular expression pattern pattern
.
REGEXP_REPLACE
Syntax:REGEXP_REPLACE( string, pattern, replace_with )
Searches for a substring in the string string
using the regular expression pattern pattern
and replaces it with the string replace_with
.
If the substring is not found, the string is not changed.
REPLACE
Syntax:REPLACE( string, substring, replace_with )
Searches for the substring substring
in the string string
and replaces it with the string replace_with
.
If the substring is not found, the string is not changed.
RIGHT
Syntax:RIGHT( string, number )
Returns a string that contains the number of characters specified in number
from the end of the string string
.
RTRIM
Syntax:RTRIM( string )
Returns the string string
without spaces at the end of the string.
SPACE
Syntax:SPACE( value )
Returns a string with the specified number of spaces.
SPLIT
Syntax:SPLIT( orig_string [ , delimiter [ , part_index ] ] )
It splits orig_string
into a sequence of substrings using the delimiter
character as separator and returns the substring whose number is equal to the part_index
parameter. By default, the delimiting character is comma. If part_index
is negative, the substring to return is counted from the end of orig_string
. If the number of substrings is less than the part_index
absolute valuepart_index
was not provided, the function returns an array of the substrings (only for ClickHouse
, PostgreSQL
sources).
STARTSWITH
Syntax:STARTSWITH( string, substring )
Returns TRUE
if string
starts with substring
. For case-insensitive searches, see ISTARTSWITH.
SUBSTR
Syntax:SUBSTR( string, from_index [ , length ] )
Returns the substring string
starting from the index from_index
. The numbering starts with one.
If an additional argument length
is specified, a substring of the specified length is returned.
TRIM
Syntax:TRIM( string )
Returns the string string
without spaces at the beginning or end of the string.
UPPER
Syntax:UPPER( string )
Returns the string string
in uppercase.
UTF8
Syntax:UTF8( string, old_encoding )
Converts the string
string encoding to UTF8
.