String functions

Reference for string functions in HotSQL. Names, signatures, and examples match the engine exactly.

ascii

Returns the first Unicode scalar value of a string.

ascii(str)

Arguments

• str: String expression.

> select ascii('abc');
+--------------------+
| ascii(Utf8("abc")) |
+--------------------+
| 97                 |
+--------------------+
> select ascii('🚀');
+-------------------+
| ascii(Utf8("🚀")) |
+-------------------+
| 128640            |
+-------------------+

Related: chr

bit_length

Returns the bit length of a string.

bit_length(str)

Arguments

• str: String expression.

Related: length, octet_length

btrim

Trims the specified trim string from the start and end of a string. If no trim string is provided, all spaces are removed from the start and end of the input string.

btrim(str[, trim_str])

Arguments

• str: String expression.
• trim_str: String expression to operate on. Can be a constant, column, or function, and any combination of operators. Default is a space.

Related: ltrim, rtrim

character_length

Returns the number of characters in a string.

character_length(str)

Arguments

• str: String expression.

> select character_length('Ångström');
+------------------------------------+
| character_length(Utf8("Ångström")) |
+------------------------------------+
| 8                                  |
+------------------------------------+

Related: bit_length, octet_length

chr

Returns a string containing the character with the specified Unicode scalar value.

chr(expression)

Arguments

• expression: String expression.

> select chr(128640);
+--------------------+
| chr(Int64(128640)) |
+--------------------+
| 🚀                 |
+--------------------+

Related: ascii

concat

Concatenates multiple strings together.

concat(str[, ..., str_n])

Arguments

• str: String expression.
• str_n: Subsequent string expressions to concatenate.

Related: concat_ws

concat_ws

Concatenates multiple strings together with a specified separator.

concat_ws(separator, str[, ..., str_n])

Arguments

• separator: Separator to insert between concatenated strings.
• str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
• str_n: Subsequent string expressions to concatenate.

> select concat_ws('_', 'data', 'fusion');
+--------------------------------------------------+
| concat_ws(Utf8("_"),Utf8("data"),Utf8("fusion")) |
+--------------------------------------------------+
| data_fusion                                      |
+--------------------------------------------------+

Related: concat

contains

Return true if search_str is found within string (case-sensitive).

contains(str, search_str)

Arguments

• str: String expression.
• search_str: The string to search for in str.

> select contains('the quick brown fox', 'row');
+---------------------------------------------------+
| contains(Utf8("the quick brown fox"),Utf8("row")) |
+---------------------------------------------------+
| true                                              |
+---------------------------------------------------+

decode

Decode binary data from textual representation in string.

decode(expression, format)

Arguments

• expression: Expression containing encoded string data
• format: Same arguments as encode

Related: encode

digest

Computes the binary hash of an expression using the specified algorithm.

digest(expression, algorithm)

Arguments

• expression: String expression.
• algorithm: String expression specifying algorithm to use. Must be one of:
  • md5
  • sha224
  • sha256
  • sha384
  • sha512
  • blake2s
  • blake2b
  • blake3

> select digest('foo', 'sha256');
+------------------------------------------------------------------+
| digest(Utf8("foo"),Utf8("sha256"))                               |
+------------------------------------------------------------------+
| 2c26b46b68ffc68ff99b453c1d30413413422d706483bfa0f98a5e886266e7ae |
+------------------------------------------------------------------+

encode

Encode binary data into a textual representation.

encode(expression, format)

Arguments

• expression: Expression containing string or binary data
• format: Supported formats are: base64, base64pad, hex

Related: decode

ends_with

Tests if a string ends with a substring.

ends_with(str, substr)

Arguments

• str: String expression.
• substr: Substring to test for.

find_in_set

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings.

find_in_set(str, strlist)

Arguments

• str: String expression to find in strlist.
• strlist: A string list is a string composed of substrings separated by , characters.

> select find_in_set('b', 'a,b,c,d');
+----------------------------------------+
| find_in_set(Utf8("b"),Utf8("a,b,c,d")) |
+----------------------------------------+
| 2                                      |
+----------------------------------------+

initcap

Capitalizes the first character in each word in the input string. Words are delimited by non-alphanumeric characters.

initcap(str)

Arguments

• str: String expression.

Related: lower, upper

left

Returns a specified number of characters from the left side of a string.

left(str, n)

Arguments

• str: String expression.
• n: Number of characters to return.

Related: right

levenshtein

Returns the Levenshtein distance between the two given strings.

levenshtein(str1, str2)

Arguments

• str1: String expression to compute Levenshtein distance with str2.
• str2: String expression to compute Levenshtein distance with str1.

> select levenshtein('kitten', 'sitting');
+---------------------------------------------+
| levenshtein(Utf8("kitten"),Utf8("sitting")) |
+---------------------------------------------+
| 3                                           |
+---------------------------------------------+

lower

Converts a string to lower-case.

lower(str)

Arguments

• str: String expression.

> select lower('Ångström');
+-------------------------+
| lower(Utf8("Ångström")) |
+-------------------------+
| ångström                |
+-------------------------+

Related: initcap, upper

lpad

Pads the left side of a string with another string to a specified string length.

lpad(str, n[, padding_str])

Arguments

• str: String expression.
• n: String length to pad to. If the input string is longer than this length, it is truncated (on the right).
• padding_str: Optional string expression to pad with. Can be a constant, column, or function, and any combination of string operators. Default is a space.

> select lpad('Dolly', 10, 'hello');
+---------------------------------------------+
| lpad(Utf8("Dolly"),Int64(10),Utf8("hello")) |
+---------------------------------------------+
| helloDolly                                  |
+---------------------------------------------+

Related: rpad

ltrim

Trims the specified trim string from the beginning of a string. If no trim string is provided, spaces are removed from the start of the input string.

ltrim(str[, trim_str])

Arguments

• str: String expression.
• trim_str: String expression to trim from the beginning of the input string. Can be a constant, column, or function, and any combination of arithmetic operators. Default is a space.

Related: btrim, rtrim

md5

Computes an MD5 128-bit checksum for a string expression.

md5(expression)

Arguments

• expression: String expression.

> select md5('foo');
+----------------------------------+
| md5(Utf8("foo"))                 |
+----------------------------------+
| acbd18db4cc2f85cedef654fccc4a4d8 |
+----------------------------------+

octet_length

Returns the length of a string in bytes.

octet_length(str)

Arguments

• str: String expression.

> select octet_length('Ångström');
+--------------------------------+
| octet_length(Utf8("Ångström")) |
+--------------------------------+
| 10                             |
+--------------------------------+

Related: bit_length, length

overlay

Returns the string which is replaced by another string from the specified position and specified count length.

overlay(str PLACING substr FROM pos [FOR count])

Arguments

• str: String expression.
• substr: Substring to replace in str.
• pos: The start position to start the replace in str.
• count: The count of characters to be replaced from start position of str. If not specified, will use substr length instead.

> select overlay('Txxxxas' placing 'hom' from 2 for 4);
+--------------------------------------------------------+
| overlay(Utf8("Txxxxas"),Utf8("hom"),Int64(2),Int64(4)) |
+--------------------------------------------------------+
| Thomas                                                 |
+--------------------------------------------------------+

regexp_count

Returns the number of matches that a regular expression has in a string.

regexp_count(str, regexp[, start, flags])

Arguments

• str: String expression.
• regexp: Regular expression.
• start: - start: Optional start position (the first position is 1) to search for the regular expression. Can be a constant, column, or function.
• flags: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported:
  • i: case-insensitive: letters match both upper and lower case
  • m: multi-line mode: ^ and $ match begin/end of line
  • s: allow . to match \n
  • R: enables CRLF mode: when multi-line mode is enabled, \r\n is used
  • U: swap the meaning of x* and x*?

> select regexp_count('abcAbAbc', 'abc', 2, 'i');
+---------------------------------------------------------------+
| regexp_count(Utf8("abcAbAbc"),Utf8("abc"),Int64(2),Utf8("i")) |
+---------------------------------------------------------------+
| 1                                                             |
+---------------------------------------------------------------+

regexp_instr

Returns the position in a string where the specified occurrence of a POSIX regular expression is located.

regexp_instr(str, regexp[, start[, N[, flags[, subexpr]]]])

Arguments

• str: String expression.
• regexp: Regular expression.
• start: - start: Optional start position (the first position is 1) to search for the regular expression. Can be a constant, column, or function. Defaults to 1
• N: - N: Optional The N-th occurrence of pattern to find. Defaults to 1 (first match). Can be a constant, column, or function.
• flags: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported:
  • i: case-insensitive: letters match both upper and lower case
  • m: multi-line mode: ^ and $ match begin/end of line
  • s: allow . to match \n
  • R: enables CRLF mode: when multi-line mode is enabled, \r\n is used
  • U: swap the meaning of x* and x*?
• subexpr: Optional Specifies which capture group (subexpression) to return the position for. Defaults to 0, which returns the position of the entire match.

> SELECT regexp_instr('ABCDEF', 'C(.)(..)');
+---------------------------------------------------------------+
| regexp_instr(Utf8("ABCDEF"),Utf8("C(.)(..)"))                 |
+---------------------------------------------------------------+
| 3                                                             |
+---------------------------------------------------------------+

regexp_like

Returns true if a regular expression has at least one match in a string, false otherwise.

regexp_like(str, regexp[, flags])

Arguments

• str: String expression.
• regexp: Regular expression.
• flags: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported:
  • i: case-insensitive: letters match both upper and lower case
  • m: multi-line mode: ^ and $ match begin/end of line
  • s: allow . to match \n
  • R: enables CRLF mode: when multi-line mode is enabled, \r\n is used
  • U: swap the meaning of x* and x*?

regexp_match

Returns the first regular expression matches in a string.

regexp_match(str, regexp[, flags])

Arguments

• str: String expression.
• regexp: Regular expression to match against. Can be a constant, column, or function.
• flags: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported:
  • i: case-insensitive: letters match both upper and lower case
  • m: multi-line mode: ^ and $ match begin/end of line
  • s: allow . to match \n
  • R: enables CRLF mode: when multi-line mode is enabled, \r\n is used
  • U: swap the meaning of x* and x*?

regexp_replace

Replaces substrings in a string that match a regular expression.

regexp_replace(str, regexp, replacement[, flags])

Arguments

• str: String expression.
• regexp: Regular expression to match against. Can be a constant, column, or function.
• replacement: Replacement string expression to operate on. Can be a constant, column, or function, and any combination of operators.
• flags: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported:
• g: (global) Search globally and don't return after the first match
• i: case-insensitive: letters match both upper and lower case
• m: multi-line mode: ^ and $ match begin/end of line
• s: allow . to match \n
• R: enables CRLF mode: when multi-line mode is enabled, \r\n is used
• U: swap the meaning of x* and x*?

repeat

Returns a string with an input string repeated a specified number.

repeat(str, n)

Arguments

• str: String expression.
• n: Number of times to repeat the input string.

> select repeat('data', 3);
+-------------------------------+
| repeat(Utf8("data"),Int64(3)) |
+-------------------------------+
| datadatadata                  |
+-------------------------------+

replace

Replaces all occurrences of a specified substring in a string with a new substring.

replace(str, substr, replacement)

Arguments

• str: String expression.
• substr: Substring expression to replace in the input string. Substring expression.
• replacement: Replacement substring expression.

> select replace('ABabbaBA', 'ab', 'cd');
+-------------------------------------------------+
| replace(Utf8("ABabbaBA"),Utf8("ab"),Utf8("cd")) |
+-------------------------------------------------+
| ABcdbaBA                                        |
+-------------------------------------------------+

reverse

Reverses the character order of a string.

reverse(str)

Arguments

• str: String expression.

right

Returns a specified number of characters from the right side of a string.

right(str, n)

Arguments

• str: String expression.
• n: Number of characters to return.

Related: left

rpad

Pads the right side of a string with another string to a specified string length.

rpad(str, n[, padding_str])

Arguments

• str: String expression.
• n: String length to pad to. If the input string is longer than this length, it is truncated.
• padding_str: String expression to pad with. Can be a constant, column, or function, and any combination of string operators. Default is a space.

Related: lpad

rtrim

Trims the specified trim string from the end of a string. If no trim string is provided, all spaces are removed from the end of the input string.

rtrim(str[, trim_str])

Arguments

• str: String expression.
• trim_str: String expression to trim from the end of the input string. Can be a constant, column, or function, and any combination of arithmetic operators. Default is a space.

Related: btrim, ltrim

sha224

Computes the SHA-224 hash of a binary string.

sha224(expression)

Arguments

• expression: String expression.

> select sha224('foo');
+----------------------------------------------------------+
| sha224(Utf8("foo"))                                      |
+----------------------------------------------------------+
| 0808f64e60d58979fcb676c96ec938270dea42445aeefcd3a4e6f8db |
+----------------------------------------------------------+

sha256

Computes the SHA-256 hash of a binary string.

sha256(expression)

Arguments

• expression: String expression.

> select sha256('foo');
+------------------------------------------------------------------+
| sha256(Utf8("foo"))                                              |
+------------------------------------------------------------------+
| 2c26b46b68ffc68ff99b453c1d30413413422d706483bfa0f98a5e886266e7ae |
+------------------------------------------------------------------+

sha384

Computes the SHA-384 hash of a binary string.

sha384(expression)

Arguments

• expression: String expression.

> select sha384('foo');
+--------------------------------------------------------------------------------------------------+
| sha384(Utf8("foo"))                                                                              |
+--------------------------------------------------------------------------------------------------+
| 98c11ffdfdd540676b1a137cb1a22b2a70350c9a44171d6b1180c6be5cbb2ee3f79d532c8a1dd9ef2e8e08e752a3babb |
+--------------------------------------------------------------------------------------------------+

sha512

Computes the SHA-512 hash of a binary string.

sha512(expression)

Arguments

• expression: String expression.

> select sha512('foo');
+----------------------------------------------------------------------------------------------------------------------------------+
| sha512(Utf8("foo"))                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------+
| f7fbba6e0636f890e56fbbf3283e524c6fa3204ae298382d624741d0dc6638326e282c41be5e4254d8820772c5518a2c5a8c0c7f7eda19594a7eb539453e1ed7 |
+----------------------------------------------------------------------------------------------------------------------------------+

split_part

Splits a string based on a specified delimiter and returns the substring in the specified position.

split_part(str, delimiter, pos)

Arguments

• str: String expression.
• delimiter: String or character to split on.
• pos: Position of the part to return (counting from 1). Negative values count backward from the end of the string.

> select split_part('1.2.3.4.5', '.', 3);
+--------------------------------------------------+
| split_part(Utf8("1.2.3.4.5"),Utf8("."),Int64(3)) |
+--------------------------------------------------+
| 3                                                |
+--------------------------------------------------+

starts_with

Tests if a string starts with a substring.

starts_with(str, substr)

Arguments

• str: String expression.
• substr: Substring to test for.

strpos

Returns the starting position of a specified substring in a string. Positions begin at 1. If the substring does not exist in the string, the function returns 0.

strpos(str, substr)

Arguments

• str: String expression.
• substr: Substring expression to search for.

substr

Extracts a substring of a specified number of characters from a specific starting position in a string.

substr(str, start_pos[, length])

Arguments

• str: String expression.
• start_pos: Character position to start the substring at. The first character in the string has a position of 1. If the start position is less than 1, it is treated as if it is before the start of the string and the (absolute) number of characters before position 1 is subtracted from length (if given). For example, substr('abc', -3, 6) returns 'ab'.
• length: Number of characters to extract. If not specified, returns the rest of the string after the start position.

substr_index

Returns the substring from str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned.

substr_index(str, delim, count)

Arguments

• str: String expression.
• delim: The string to find in str to split str.
• count: The number of times to search for the delimiter. Can be either a positive or negative number.

> select substr_index('www.apache.org', '.', 1);
+---------------------------------------------------------+
| substr_index(Utf8("www.apache.org"),Utf8("."),Int64(1)) |
+---------------------------------------------------------+
| www                                                     |
+---------------------------------------------------------+
> select substr_index('www.apache.org', '.', -1);
+----------------------------------------------------------+
| substr_index(Utf8("www.apache.org"),Utf8("."),Int64(-1)) |
+----------------------------------------------------------+
| org                                                      |
+----------------------------------------------------------+

to_hex

Converts an integer to a hexadecimal string.

to_hex(int)

Arguments

• int: Integer expression.

> select to_hex(12345689);
+-------------------------+
| to_hex(Int64(12345689)) |
+-------------------------+
| bc6159                  |
+-------------------------+

translate

Performs character-wise substitution based on a mapping.

translate(str, from, to)

Arguments

• str: String expression.
• from: The characters to be replaced.
• to: The characters to replace them with. Each character in from that is found in str is replaced by the character at the same index in to. Any characters in from that don't have a corresponding character in to are removed. If a character appears more than once in from, the first occurrence determines the mapping.

> select translate('twice', 'wic', 'her');
+--------------------------------------------------+
| translate(Utf8("twice"),Utf8("wic"),Utf8("her")) |
+--------------------------------------------------+
| there                                            |
+--------------------------------------------------+

upper

Converts a string to upper-case.

upper(str)

Arguments

• str: String expression.

Related: initcap, lower

uuid

Returns UUID v4 string value which is unique per row.

uuid()

> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 6ec17ef8-1934-41cc-8d59-d0c8f9eea1f0 |
+--------------------------------------+