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.

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 value, the function returns an empty string. If part_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.