> ## Documentation Index > Fetch the complete documentation index at: https://docs.kinetica.com/llms.txt > Use this file to discover all available pages before exploring further. # String Functions There are four different types of string functions: * [Scalar Functions](/content/sql/query/string-functions#sql-string-functions-scalar) * [Aggregation Functions](/content/sql/query/string-functions#sql-string-functions-agg) * [LIKE / ILIKE](/content/sql/query/string-functions#sql-string-functions-like) * [FILTER\_BY\_STRING](/content/sql/query/string-functions#sql-string-functions-fts) String columns are stored as byte arrays, but allow multi-byte characters, as they are UTF-8 encoded. Some functions may behave in unexpected ways when given multi-byte input. ## Scalar Functions Returns the ASCII code for the first byte in `expr` Convert the value represented by `expr` into a binary string representation. Use optional `minimum_digits` to add leading 0s when needed; defaults to 1. Examples:

Function Call	Result
`BIN(42)`	`'101010'`
`BIN(42, 8)`	`'00101010'`

The character represented by the standard ASCII code `expr` in the range \[ `0` - `127` ] Performs a string concatenation of `expr_a` & `expr_b`; use nested `CONCAT` calls to concatenate more than two strings The resulting field size of any `CONCAT` will be a `VARCHAR(N)` field big enough to hold the concatenated fields, e.g., concatenating a `VARCHAR(32)` column and a `VARCHAR(64)` column will result in a `VARCHAR(128)` column. Columns of type `VARCHAR(256)` used with `CONCAT` will result in a `VARCHAR(256)` column, truncated at 256 characters. The `||` operator can be used in place of `CONCAT` to perform string concatenation; e.g., the following two expressions are equivalent: ``` expr_a || expr_b || expr_c ``` ``` CONCAT(CONCAT(expr_a, expr_b), expr_c) ``` Examples:

Function Call	Result
`CONCAT(CONCAT('ABC','123'),'!')`	`ABC123!`
`'ABC' \|\| '123' \|\| '!'`	`ABC123!`

Returns the concatenation of `expr_a` and `expr_b`, truncated at the maximum size of the larger of `expr_a` and `expr_b`. For data columns, the size is explicit; for string constants, the size will be the smallest *charN* type that can hold the constant string. `CONCAT_TRUNCATE` will not work if any parameter is an unrestricted-width string. Examples:

Function Call	Result
`CONCAT\_TRUNCATE('ABC123','!')`	`ABC123!`
`CONCAT\_TRUNCATE('AB','CDE')`	`ABCD` (char4 is the minimum size required to hold the `CDE` value, so the result is truncated at 4 characters)
`CONCAT\_TRUNCATE('ABCD1234','DEFG')`	`ABCD1234` (an 8-character string is the minimum size required to hold the `ABCD1234` value, so no characters can be appended)

Returns `1` if `expr` contains `match` by string-literal comparison; otherwise, returns `0` Convert the value represented by the `expr` string from one numeric base to another. Base parameters may be \[`2` - `36`]. Binary, octal and hexadecimal strings may have an optional prefix. Examples:

Function Call	Result
`CONV('42', 10, 16)`	`'2A'`
`CONV('2a', 16, 10)`	`'42'`
`CONV('11', 2, 10)`	`'3'`
`CONV('0o52', 8, 2)`	`'101010'`

Alias for `HEX` Returns a value between `0` and `4` that represents the difference between the sounds of `expr_a` and `expr_b` based on the `SOUNDEX()` value of the strings--a value of `4` is the best possible sound match Returns the Levenshtein edit distance between `expr_a` and `expr_b`; the lower the value, the more similar the two strings are Returns `1` if `expr` ends with `match` by string-literal comparison; otherwise, returns `0` Alias for `UNHEX` Convert the value represented by `expr` into a hexadecimal string representation. Use optional `minimum_digits` to add leading 0s when needed; defaults to 1. Examples:

Function Call	Result
`HEX(42)`	`'2A'`
`HEX(42, 8)`	`'0000002A'`

Alias for `UNHEX` Returns `expr` with the first letter of each word in uppercase Returns the octet of the IP address given in `expr` at the position specified by `part_num`. Valid `part_num` values are constants from `1` to `4`. Examples:

Function Call	Result
`IPV4\_PART('12.34.56.78', 1)`	`12`
`IPV4\_PART('12.34.56.78', 4)`	`78`

Returns `1` if `expr` is an IPV4 address; returns `0` otherwise Alias for `IS_IPV4` Converts `expr` to lowercase Returns the leftmost `num_bytes` bytes from `expr` Alias for `LENGTH` Returns the number of characters in `expr` Returns the starting position of the first match of `match` in `expr`, starting from position 1 or `start_pos` (if specified). If `match` can't be found or `start_pos` is outside the range of letters in `expr`, a `0` is returned. Alias for `LCASE` Left pads the given `expr` string with the `pad` string to the given `length` of bytes. If `expr` is longer than `length`, the return value is shortened to `length` bytes. If `length` is larger than 256, it will be truncated to 256 bytes. The default padding character is a space. The use of multi-byte characters in this function may have unexpected results. Examples:

Function Call	Result
`LPAD('test', 9, 'pad')`	`padpatest`
`LPAD('test', 3, 'pad')`	`tes`

Removes whitespace from the left side of `expr` Convert the value represented by `expr` into an octal (base 8) string representation. Use optional `minimum_digits` to add leading 0s when needed; defaults to 1. Examples:

Function Call	Result
`OCT(42)`	`'52'`
`OCT(42, 8)`	`'00000052'`

Alias for `SIZE_PRETTY` Returns the starting position of the first match of `match` in `expr`, starting from position 1. If `match` can't be found, a `0` is returned. Returns a count of the number of times the `regex` pattern is matched in `expr`. Matches do not overlap, so the start of a future match must start after the end of the previous match. The `regex` parameter is the regular expression to try to match. It must be a string-literal with 256 characters or fewer. Generally, POSIX-compliant regular expressions are supported for `regex`. The escape character used to match wildcards in the `expr` literally is `\`. See `REGEXP_LIKE` for more regular expression examples. The optional `position` parameter specifies where to start searching in `expr` for the first match. The first character in the string has a `position` of `1` (the default). The optional `mode` parameter is a string which can be empty (the default) for the default behavior. See `REGEXP_LIKE` for the list of supported mode flags. Examples of `REGEXP_COUNT`:

Function Call	Result
`REGEXP\_COUNT('abababab', 'abab')`	2
`REGEXP\_COUNT('abababab', 'abab', 2)`	1
`REGEXP\_COUNT('don''t', 'DON''T', 1, 'i')`	1

Returns the starting position (1-based) in `expr` where a `regex` match is found. If no match is found, `0` is returned. The `regex` parameter is the regular expression to try to match. It must be a string-literal with 256 characters or fewer. Generally, POSIX-compliant regular expressions are supported for `regex`. The escape character used to match wildcards in the `expr` literally is `\`. See `REGEXP_LIKE` for more regular expression examples. The optional `position` parameter specifies where to start searching in `expr` for the first match. The first character in the string has a `position` of `1` (the default). The optional `occurrence` parameter specifies which occurrence of the `regex` match is desired. For example, `2` would return the second occurrence of `regex` in `expr`. Matches do not overlap, so the start of a future match must start after the end of the previous match. The optional `begin_end` parameter specifies if the beginning or ending position is desired. Use `0` (the default) for the beginning of the match and use `1` for the position after the end of the match. The optional `mode` parameter is a string which can be empty (the default) for the default behavior. See `REGEXP_LIKE` for the list of supported mode flags. The optional `group` parameter specifies which regular expression group's (i.e., parentheses inside `regex`) beginning/ending position to return. The default of `0` uses the entire matched expression, while a `group` of `1` through `9` corresponds to the 1st group up through the 9th group of the match. Examples of `REGEXP_INSTR`:

Function Call	Result
`REGEXP\_INSTR('abcdefg', 'bc')`	2
`REGEXP\_INSTR('abcdefg', 'ab', 2)`	0
`REGEXP\_INSTR('abcabc', 'a', 1, 2)`	4
`REGEXP\_INSTR('abcabc', 'abc', 1, 1, 1)`	4
`REGEXP\_INSTR('abcabc', 'B C', 1, 1, 0, 'ix')`	2
`REGEXP\_INSTR('abcdefg', '(C(.(.)))', 1, 1, 0, 'i', 3)`	5

Returns whether `expr` matches the given `regex`. Generally, POSIX-compliant regular expressions are supported. The optional `mode` parameter is a string which can be empty (the default) for the default behavior. It can contain the following letters for the associated optional modified behaviors:

Letter	Meaning
`i`	Case-insensitive matches
`m`	Treat input as multiple lines so `^` and `\$` match around newlines and not just the beginning and ending of the string
`n`	Allows `.` to also match a newline character
`x`	Ignore any whitespace (e.g., spaces) in `regex`

The escape character used to match wildcards in the `expr` literally is `\`. The `regex` can match the `expr` partially. To perform full matches, `^` and `$` can be used to match the start and end of `expr`, respectively. Examples of successful matches:

Function Call	Match Type
`REGEXP\_LIKE('partial', 'part')`	Partial
`REGEXP\_LIKE('Case', 'cAsE', 'i')`	Case-insensitive
`REGEXP\_LIKE('dot', 'd.t')`	Any character
`REGEXP\_LIKE('range', 'ra\[a-z]ge')`	Character range
`REGEXP\_LIKE('zeroorone', 'z?zer(oor)?one')`	0 or 1 token
`REGEXP\_LIKE('zeroormore', 'z\zer(\[om]or)\e')`	0 or more tokens
`REGEXP\_LIKE('oneormore', 'o+n(\[em]or)+e')`	1 or more tokens
`REGEXP\_LIKE('A to Z', '^A.\*Z\$')`	Begin/End
`REGEXP\_LIKE('41ph4Num', '^\[\[:alnum:]]+\$')`	Character class
`REGEXP\_LIKE('Escape?', 'Escape?')`	Escape wildcard

Alias for `REGEXP_LIKE` Returns the `expr` string after replacing `regex` matches with the `replace` string parameter. The `regex` parameter is the regular expression to try to match. It must be a string-literal with 256 characters or fewer. Generally, POSIX-compliant regular expressions are supported for `regex`. The escape character used to match wildcards in the `expr` literally is `\`. See `REGEXP_LIKE` for more regular expression examples. The `replace` parameter is the optional text with which to replace each match of `regex` in `expr`. The default is an empty string, which will simply remove the specified occurrences of `regex` in `expr`. A `replace` of `\0` will insert the entire matched expression, while a `replace` of `\1` through `\9` will use the corresponding matched grouping (parentheses inside `regex`) as the replacement text. The optional `position` parameter specifies where to start searching in `expr` for the first match. The first character in the string has a `position` of `1` (the default). The optional `occurrence` parameter specifies which occurrence of the `regex` match to replace. For example, `2` would only replace the second occurrence of `regex` in `expr`. Matches do not overlap, so the start of a future match must start after the end of the previous match. Use `0` (the default) to replace all occurrences. The optional `mode` parameter is a string which can be empty (the default) for the default behavior. See `REGEXP_LIKE` for the list of supported mode flags. Examples of `REGEXP_REPLACE`:

Function Call	Result
`REGEXP\_REPLACE('abc', 'b')`	ac
`REGEXP\_REPLACE('abc', 'b.', 'x')`	ax
`REGEXP\_REPLACE('abcd', '.', 'x', 3, 1)`	abxd
`REGEXP\_REPLACE('abcd', '(b(.))', '-\2-')`	a-c-d

Returns the portion of the `expr` string that matched `regex`. An empty string is returned if no match is found. The `regex` parameter is the regular expression to try to match. It must be a string-literal with 256 characters or fewer. Generally, POSIX-compliant regular expressions are supported for `regex`. The escape character used to match wildcards in the `expr` literally is `\`. See `REGEXP_LIKE` for more regular expression examples. The optional `position` parameter specifies where to start searching in `expr` for the first match. The first character in the string has a `position` of `1` (the default). The optional `occurrence` parameter specifies which occurrence of the `regex` match is desired. For example, `2` would return the second occurrence of `regex` in `expr`. Matches do not overlap, so the start of a future match must start after the end of the previous match. The optional `mode` parameter is a string which can be empty (the default) for the default behavior. See `REGEXP_LIKE` for the list of supported mode flags. The optional `group` parameter specifies which regular expression grouping (i.e., parentheses inside `regex`) to use. The default of `0` uses the entire matched expression, while a `group` of `1` through `9` corresponds to the 1st group up through the 9th group of the match. Examples of `REGEXP_SUBSTR`:

Function Call	Result
`REGEXP\_SUBSTR('abcdefg', 'b.')`	bc
`REGEXP\_SUBSTR('abcadeafghij', 'a...', 2, 1)`	adea
`REGEXP\_SUBSTR('abcadeafghij', 'a...', 1, 2)`	afgh
`REGEXP\_SUBSTR('abcdefg', '(C(.(.)))', 1, 1, 'i', 3)`	e

Replaces every occurrence of `match` in `expr` with `repl` Replaces every occurrence of the single-byte character `match` in `expr` with the single-byte character `repl` Replaces every occurrence of `match` in `expr` with `repl`, and then truncates the resulting string at 256 bytes if it is longer than that `REPLACE_TRUNCATE` will not work if any parameter is an unrestricted-width string. The use of multi-byte characters in this function may have unexpected results. Returns `expr` with the order of bytes reversed. The use of multi-byte characters in this function may have unexpected results. Examples:

Function Call	Result
`REVERSE('Reverse')`	`esreveR`
`REVERSE('Was it a bat I saw?')`	`?was I tab a ti saW`

Returns the rightmost `num_bytes` bytes from `expr` Right pads the given `expr` string with the `pad` string to the given `length` of bytes. If `expr` is longer than `length`, the return value is shortened to `length` bytes. If `length` is larger than 256, it will be truncated to 256 bytes. The default padding character is a space. The use of multi-byte characters in this function may have unexpected results. Examples:

Function Call	Result
`RPAD('test', 9, 'pad')`	`testpadpa`
`RPAD('test', 3, 'pad')`	`tes`

Removes whitespace from the right side of `expr` Convert the byte count `expr` into a human-readable size string using 1024-based units (`bytes`, `KB`, `MB`, `GB`, `TB`, `PB`). The unit is selected so the printed number should have at most 3 digits. Examples:

Function Call	Result
`SIZE\_PRETTY(2048)`	`'2 KB'`
`SIZE\_PRETTY(10485760)`	`'10 MB'`
`SIZE\_PRETTY(1073741824)`	`'1 GB'`

Returns a soundex value from `expr`. Only the first word in the string will be considered in the calculation. This is the algorithm used by most programming languages. Returns a string consisting of `n` space characters. The value of `n` can only be within the range of 0-256. Splits `expr` into groups delimited by the `delim` single-byte character and returns the `group_num` split group. If `group_num` is positive, groups will be counted from the beginning of `expr`; if negative, groups will be counted from the end of `expr` going backwards. Two consecutive delimiters will result in an empty string being added to the list of selectable groups. If no instances of `delim` exist in `expr`, the entire string is available at group `1` (and `-1`). Group `0` returns nothing. Examples:

Function Call	Result
`SPLIT('apple', 'p', 1)`	`a`
`SPLIT('apple', 'p', 2)`	\
`SPLIT('apple', 'p', -1)`	`le`

Returns `1` if `expr` starts with `match` by string-literal comparison; otherwise, returns `0` Compares `expr_a` to `expr_b` in a lexicographical sort

Situation	Result
`expr\_a` and `expr\_b` are the same	`0`
`expr\_a` comes before `expr\_b`, lexicographically	`-1`
`expr\_a` comes after `expr\_b`, lexicographically	`1`

Alias for `SUBSTRING` Returns `num_bytes` bytes from the `expr`, starting at the 1-based `start_pos` byte. If `num_bytes` is not specified, all bytes after `start_pos` will be returned. The use of multi-byte characters in this function may have unexpected results. Examples:

Function Call	Result
`SUBSTRING('banana', 3)`	`nana`
`SUBSTRING('banana', 3, 2)`	`na`

Alias for `HEX` Removes whitespace from both sides of `expr` Converts `expr` to uppercase Convert the hexadecimal string `expr` into a (decimal) number. Examples:

Function Call	Result
`UNHEX('42')`	`66`
`UNHEX('1A')`	`26`
`UNHEX('1a')`	`26`

Alias for `UCASE` ## Aggregation Functions The following functions can be used on *string* columns within [aggregations](/content/sql/query/aggregation#sql-aggregation). These functions can be used to convert string column values into delimited lists of those values. Combines column values within a group into a single delimited *string* of those values. The default delimiter is a comma. Combines column values within a group into a single delimited *string* of those values, removing any duplicates. The default delimiter is a comma. ## LIKE / ILIKE Simple pattern-matching capability is provided through the use of the `LIKE` and `ILIKE` clauses, for [string columns](/content/sql/ddl/column-types#sql-column-types). `LIKE` is case-sensitive, while `ILIKE` is case-insensitive. ```sql title="LIKE / ILIKE Syntax" theme={null} [NOT] [ESCAPE ''] ``` This clause matches records where reference expression `expr` does (or does `NOT`) match the string value of `match`. The match is a string literal one, with the following exceptions: * `%` matches any string of 0 or more characters * `_` matches any single character * `[]` matches any single character listed in the set. The `character_set` may contain ranges using a dash (`-`). A `character_set` starting with `^` will match any character not in the specified set. By default, there is no escape character. Use the `ESCAPE` clause to specify an escape character. The escape character is used to match special characters in the `expr` literally. For example, `~%` will match the `%` character if `~` is the escape character. To search for employees whose last name starts with *C*, ends with *a*, and has exactly five letters: ```sql LIKE Single Character Case-Sensitive Match Example theme={null} SELECT * FROM example.employee WHERE last_name LIKE 'C___a' ``` To search for employees whose first name does not start with *Brook*, regardless of case: ```sql ILIKE Multiple Character Case-Insensitive Match Example theme={null} SELECT * FROM example.employee WHERE first_name NOT ILIKE 'brook%' ``` To search for employees whose first name has a capital letter after the first letter: ```sql LIKE Character Range Case-Sensitive Match Example theme={null} SELECT * FROM example.employee WHERE first_name LIKE '%_[A-Z]%' ``` To search for employees whose first name does not contain an underscore, redefining the default escape character to a `~` to escape the underscore: ```sql ILIKE Custom Escape Character Case-Insensitive Match Example theme={null} SELECT * FROM example.employee WHERE first_name NOT ILIKE '%~_%' ESCAPE '~' ``` ## FILTER\_BY\_STRING The `FILTER_BY_STRING` table function allows for several types of pattern matching for [fixed-width string columns](/content/sql/ddl/column-types#sql-column-types) (`VARCHAR(1)` - `VARCHAR(256)`), as well as [full text search](/content/concepts/full_text_search) capability for all string columns with the `TEXT_SEARCH` [column property](/content/sql/ddl/column-properties#sql-column-properties). Its features are based on the [/filter/bystring](/content/api/rest/filter_bystring_rest) endpoint. The basic form of the `FILTER_BY_STRING` function when called in a `SELECT` statement follows. ```sql title="FILTER_BY_STRING Table Function Syntax" theme={null} SELECT * FROM TABLE ( FILTER_BY_STRING ( TABLE_NAME => INPUT_TABLE(), [COLUMN_NAMES => '',] MODE => '', EXPRESSION => '', [OPTIONS => KV_PAIRS('

), VIEW_NAME => '[.]', [COLUMN_NAMES => '',] MODE => '', EXPRESSION => '', [OPTIONS => KV_PAIRS('

Mode	Description
`contains`	Partial substring match (not accelerated). If the column is a string type (non-charN) and the number of records is too large, it will return 0.
`equals`	Exact whole-string match (accelerated).
`regex`	Full regular expression search (not accelerated). If the column is a string type (non-charN) and the number of records is too large, it will return 0.
`search`	Full text search with wildcards and boolean operators, for string columns (`VARCHAR`) that have the `TEXT\_SEARCH` [column property](/content/sql/ddl/column-properties#sql-column-properties) applied. See [Full Text Search](/content/concepts/full_text_search) for syntax & grammar detail. Omit the `COLUMN_NAMES` parameter when using this `MODE`. All text-searchable string columns will be searched. Also, only a table name can be used for `TABLE_NAME`--a query cannot be used.
`starts\_with`	Match strings that start with the given expression (not accelerated). If the column is a string type (non-charN) and the number of records is too large, it will return 0.

String literal or pattern, depending on `MODE` selected, to use in filtering string columns Optional list of string filtering options, specified as a set of key/value pairs passed as a comma-delimited list of ` = ''` assignments to the `KV_PAIRS` function; e.g.: ``` KV_PAIRS(case_sensitive = 'false') ```

Option	Description
`case\_sensitive`	If true, the filter will be case-sensitive; if false, case-insensitive. Not applicable when `MODE` is `search`.

To see the message text & time for events in the *event\_log* table containing the word *ERROR*: ```sql FILTER_BY_STRING Example theme={null} SELECT * FROM TABLE ( FILTER_BY_STRING ( TABLE_NAME => INPUT_TABLE(SELECT event_time, message FROM example.event_log), COLUMN_NAMES => 'message', MODE => 'contains', EXPRESSION => 'ERROR' ) ) ```