SQL Reference > String Functions

String Functions

This page covers functions to manipulate strings in Rockset.

Basic Functions

CONCAT

CONCAT(x, ...)

Returns concatenated strings given in the arguments. Supports variable number of arguments.

x || ...

Operator form equivalent to CONCAT.

LENGTH

LENGTH(x)

Returns length of string x.

LOWER

LOWER(x)

Returns locale-independent lowercase string x.

LTRIM

LTRIM(string)

Returns string with leading whitespace removed.

NORMALIZE

NORMALIZE(string[, form])

Returns Unicode-normalized form of string. form is an identifier and must be one of NFC, NFD, NFKC, NFKD, which are the four Unicode normalization methods; NFC is default.

Note that NORMALIZE uses special syntax; form is an identifier, not a string.

> NORMALIZE('hello', NFC)
'hello'

UPPER

UPPER(x)

Returns locale-independent uppercase string x.

REPLACE

REPLACE(string, search[, replacement])

Returns a string with all instances of search replaced with replacement in string. replacement is optional, which if not specified removes all instance of search from string.

RTRIM

RTRIM(string)

Returns string with trailing whitespace removed.

SPLIT

SPLIT(string, delimiter[, limit])

Splits string on delimiter and returns an array. With limit, returns an array of size at most limit. The last element in the array always contains everything left in the string. limit must be a positive number.

STRPOS

STRPOS(string, substring)

Returns the starting position of the first instance of substring in string. Positions start with 1. If not found, 0 is returned.

SUBSTR

SUBSTR(string, start[, length])

Returns substring of string starting at character at index given by start (1-based index) and of length length. If length is not given, returns the substring starting at start until the end of string.

> SELECT SUBSTR('abcdef', 2, 3)
'bcd'

TRIM

TRIM(string)

Returns string with leading and trailing whitespace removed.

Encoding Functions

FROM_HEX

FROM_HEX(s)

Decodes the hex string s into a bytes value.

FROM_UTF8

FROM_UTF8(b)

If b is a bytes value that represents a valid UTF-8 string, return it as a string. Otherwise, raise an error.

TO_HEX

TO_HEX(b)

Encodes the bytes value b into a hex string representation.

TO_UTF8

TO_UTF8(s)

Return the bytes UTF-8 representation of the string value s.

Regular Expression Functions

Rockset uses RE2 syntax for specifying regular expressions. Named capturing groups is not supported, only numbered groups; \0 refers to the entire match.

REGEXP_EXTRACT

REGEXP_EXTRACT(string, pattern[, group])

Returns the first match of pattern in string, or null if the pattern does not match. If group is specified and greater than zero, returns the groupth capturing group; if group is not specified or is zero, returns the full match.

REGEXP_EXTRACT_ALL

REGEXP_EXTRACT_ALL(string, pattern[, group])

Returns an array containing all matches of pattern in string. If group is specified and greater than zero, extracts the groupth capturing group from each match; if group is not specified or is zero, returns the full matches.

REGEXP_LIKE

REGEXP_LIKE(string, pattern)

Returns true if string matches the regular expression pattern, false otherwise.

REGEXP_REPLACE

REGEXP_REPLACE(string, pattern[, replacement])

Returns string with all places where pattern matches replaced with replacement (or erased if replacement is not specified). replacement may use \1\9 escape sequences to refer to capturing groups, or \0 to refer to the entire match.

REGEXP_SPLIT

REGEXP_SPLIT(string, pattern)

Returns an array with the components of string when split by pattern.