Skip to content

String functions

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

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

XTQL SQL

(character-length s)

CHARACTER_LENGTH(s)

CHAR_LENGTH(s)

length of string, in UTF8 characters

(like str like-pattern)

str [NOT] LIKE like_pattern

Returns true iff the str matches (/ doesn’t match) the like-pattern.

  • _ matches any single character

  • % matches 0-n characters

(like-regex str regex flags)

str [NOT] LIKE_REGEX regex

str [NOT] LIKE_REGEX regex FLAG flag

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(str)

lower-case

(overlay str replacement start-pos <length>)

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

(position search str)

POSITION(search IN str [USING CHARACTERS])

position of search within str, in characters

  • Return value is 1-based.

  • Returns 0 if not found.

(trim str <trim-char>)

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 str <trim-char>)

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 str <trim-char>)

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)

OCTET_LENGTH(s)

length of string, in octets

(octet-position search str)

POSITION(search IN str USING OCTETS)

position of search within str, in octets

Returns 0 if not found.

(substring str from-pos)

(substring str from-pos length)

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(str)

upper-case