String Functions - Kinetica Docs

There are four different types of string functions:

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

ASCII(expr)

Returns the ASCII code for the first byte in expr

BIN(expr[, minimum_digits])

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’`

CHAR(expr)

The character represented by the standard ASCII code expr in the range [ 0 - 127 ]

CONCAT(expr_a, expr_b)

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!`

CONCAT_TRUNCATE(expr_a, expr_b)

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)

CONTAINS(match, expr)

Returns 1 if expr contains match by string-literal comparison; otherwise, returns 0

CONV(expr, from_base, to_base)

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’`

DEC2HEX(expr[, minimum_digits])

Alias for HEX

DIFFERENCE(expr_a, expr_b)

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

EDIT_DISTANCE(expr_a, expr_b)

Returns the Levenshtein edit distance between expr_a and expr_b; the lower the value, the more similar the two strings are

ENDS_WITH(match, expr)

Returns 1 if expr ends with match by string-literal comparison; otherwise, returns 0

FROM_HEX(expr)

Alias for UNHEX

HEX(expr[, minimum_digits])

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’`

HEX2DEC(expr)

Alias for UNHEX

INITCAP(expr)

Returns expr with the first letter of each word in uppercase

IPV4_PART(expr, part_num)

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`

IS_IPV4(expr)

Returns 1 if expr is an IPV4 address; returns 0 otherwise

ISIPV4(expr)

Alias for IS_IPV4

LCASE(expr)

Converts expr to lowercase

LEFT(expr, num_bytes)

Returns the leftmost num_bytes bytes from expr

LEN(expr)

Alias for LENGTH

LENGTH(expr)

Returns the number of characters in expr

LOCATE(match, expr[, start_pos])

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.

LOWER(expr)

Alias for LCASE

LPAD(expr, length[, pad])

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`

LTRIM(expr)

Removes whitespace from the left side of expr

OCT(expr[, minimum_digits])

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’`

PG_SIZE_PRETTY(expr)

Alias for SIZE_PRETTY

POSITION(match IN expr)

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.

REGEXP_COUNT (expr, regex[, position [, mode]])

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

REGEXP_INSTR (expr, regex [, position [, occurrence [, begin_end [, mode [,group]]]]])

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

REGEXP_LIKE(expr, regex[, mode])

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’, ‘zzer([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

REGEXP_MATCH(expr, regex[, options])

Alias for REGEXP_LIKE

REGEXP_REPLACE (expr, regex [, replace [, position [, occurrence [, mode]]]])

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

REGEXP_SUBSTR (expr, regex [, position [, occurrence [, mode [,group]]]])

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

REPLACE(expr, match, repl)

Replaces every occurrence of match in expr with repl

REPLACE_CHAR(expr, match, repl)

Replaces every occurrence of the single-byte character match in expr with the single-byte character repl

REPLACE_TRUNCATE(expr, match, 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.

REVERSE(expr)

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`

RIGHT(expr, num_bytes)

Returns the rightmost num_bytes bytes from expr

RPAD(expr, length, pad)

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`

RTRIM(expr)

Removes whitespace from the right side of expr

SIZE_PRETTY(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’`

SOUNDEX(expr)

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.

SPACE(n)

Returns a string consisting of n space characters. The value of n can only be within the range of 0-256.

SPLIT(expr, delim, group_num)

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)`	<empty string>
`SPLIT(‘apple’, ‘p’, -1)`	`le`

STARTS_WITH(match, expr)

Returns 1 if expr starts with match by string-literal comparison; otherwise, returns 0

STRCMP(expr_a, expr_b)

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`

SUBSTR(expr, start_pos[, num_chars])

Alias for SUBSTRING

SUBSTRING(expr, start_pos[, num_bytes])

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`

TO_HEX(expr[, minimum_digits])

Alias for HEX

TRIM(expr)

Removes whitespace from both sides of expr

UCASE(expr)

Converts expr to uppercase

UNHEX(expr)

Convert the hexadecimal string expr into a (decimal) number.Examples:

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

UPPER(expr)

Alias for UCASE

Aggregation Functions

The following functions can be used on string columns within aggregations. These functions can be used to convert string column values into delimited lists of those values.

STRING_AGG(expr[, delim])

Combines column values within a group into a single delimited string of those values.The default delimiter is a comma.

STRING_AGG_DISTINCT(expr[, delim])

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. LIKE is case-sensitive, while ILIKE is case-insensitive.

LIKE / ILIKE Syntax

<expr> [NOT] <LIKE | ILIKE> <match> [ESCAPE '<esc_char>']

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
[<character_set>] 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:

LIKE Single Character Case-Sensitive Match Example

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:

ILIKE Multiple Character Case-Insensitive Match Example

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:

LIKE Character Range Case-Sensitive Match Example

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:

ILIKE Custom Escape Character Case-Insensitive Match Example

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 (VARCHAR(1) - VARCHAR(256)), as well as full text search capability for all string columns with the TEXT_SEARCH column property. Its features are based on the /filter/bystring endpoint. The basic form of the FILTER_BY_STRING function when called in a SELECT statement follows.

FILTER_BY_STRING Table Function Syntax

SELECT *
FROM TABLE
(
	FILTER_BY_STRING
	(
		TABLE_NAME => INPUT_TABLE(<table name | select statement>),
		[COLUMN_NAMES => '<column list>',]
		MODE => '<filter type>',
		EXPRESSION => '<filter expression>',
		[OPTIONS => KV_PAIRS('<option name>' = '<option value>'[,...])]
	)
)

The basic form of the FILTER_BY_STRING function when called in an EXECUTE FUNCTION statement follows. When called this way, the results are saved in the specified view.

FILTER_BY_STRING Execute Function Syntax

EXECUTE FUNCTION FILTER_BY_STRING
(
	TABLE_NAME => INPUT_TABLE(<table name | select statement>),
	VIEW_NAME => '[<schema name>.]<view name>',
	[COLUMN_NAMES => '<column list>',]
	MODE => '<filter type>',
	EXPRESSION => '<filter expression>',
	[OPTIONS => KV_PAIRS('<option name>' = '<option value>'[,...])]
)

TABLE_NAME

The data set to filter, which should be passed via query to the INPUT_TABLE function.To perform a string filter on a column in the customer table, pass the name of the table to INPUT_TABLE:

INPUT_TABLE(customer)

To perform a string filter on a column in the result of a query, pass the query to INPUT_TABLE:

INPUT_TABLE(SELECT * FROM customer_west UNION SELECT * FROM customer_east)

VIEW_NAME

Name of the view in which the results are to be stored, in [schema_name.]table_name format, using standard name resolution rules and meeting table naming criteria

This parameter is only applicable when using EXECUTE FUNCTION syntax.

COLUMN_NAMES

Comma-separated list of columns to which the filter will be applied.

Omit this parameter when using the search filter MODE.

MODE

Type of string filter to apply:

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 applied. See 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.

EXPRESSION

String literal or pattern, depending on MODE selected, to use in filtering string columns

OPTIONS

Optional list of string filtering options, specified as a set of key/value pairs passed as a comma-delimited list of <key> = '<value>' 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:

FILTER_BY_STRING Example

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'
    )
)

​Scalar Functions

​Aggregation Functions

​LIKE / ILIKE

​FILTER_BY_STRING

Scalar Functions

Aggregation Functions

LIKE / ILIKE

FILTER_BY_STRING