Skip to content
XTDB

String functions

Notes:

  • String positions are 1-based, in line with the SQL spec.

  • If any argument to these functions is null, the function will return null.

CHARACTER_LENGTH(s) | CHAR_LENGTH(s)

length of string, in UTF8 characters

str [NOT] LIKE like_pattern

Returns true iff the str matches (/ doesn’t match) the like_pattern.

like_pattern can contain:

  • _: matches any single character

  • %: matches 0-n characters

str [NOT] LIKE_REGEX regex

Returns true iff the str matches (/ doesn’t match) the regex.

  • Regexes use Java’s Pattern syntax.

  • Flags (string, e.g. 'im'):

    • s: 'dot' matches any character (including line terminators)

    • i: case insensitive

    • m: multi-line

LOWER(str)

lower-case

OVERLAY(str PLACING replacement FROM start_pos [FOR length])

replace length characters of str starting at start_pos with replacement

  • start_pos: 1-based start position

  • length: defaults to end-of-string if not provided

POSITION(search IN str [USING CHARACTERS])

position of search within str, in characters

  • Return value is 1-based.

  • Returns 0 if not found.

TRIM([trim_char FROM] str) | TRIM(BOTH [trim_char] FROM str)

remove any occurrences of trim_char from the start and end of str

  • trim_char: single character (defaults to 'space').

TRIM(LEADING [trim_char] FROM str)

remove any occurrences of trim_char from the start of str

  • trim_char: single character (defaults to 'space').

TRIM(TRAILING [trim_char] FROM str)

remove any occurrences of trim_char from the end of `str

  • trim_char: single character (defaults to 'space').

OCTET_LENGTH(s)

length of string, in octets

POSITION(search IN str USING OCTETS)

position of search within str, in octets

Returns 0 if not found.

SUBSTRING(str FROM from_pos) | SUBSTRING(str FROM from_pos FOR length)

Returns the sub-string of the given str from from_pos for length characters

  • from_pos: 1-based start position

  • length: defaults to end-of-string if not provided

UPPER(str)

upper-case