String functions

Functions taking a string as input all operate on Unicode characters rather than on a standard char[]. For example, the size() function applied to any Unicode character will return 1, even if the character does not fit in the 16 bits of one char.

The functions lower() and upper() have been superseded by toLower() and toUpper(), respectively, and will be removed in a future release.

When toString() is applied to a temporal value, it returns a string representation suitable for parsing by the corresponding temporal functions. This string will therefore be formatted according to the ISO 8601 format.

See also String operators.

Functions:

left()

left() returns a string containing the specified number of leftmost characters of the original string.

Syntax: left(original, length)

Returns:

A String.

Arguments:

Name Description

original

An expression that returns a string.

n

An expression that returns a positive integer.

Considerations:

left(null, length) and left(null, null) both return null

left(original, null) will raise an error.

If length is not a positive integer, an error is raised.

If length exceeds the size of original, original is returned.
Query
RETURN left('hello', 3)
Table 1. Result
left('hello', 3)

"hel"

1 row

ltrim()

lTrim() returns the original string with leading whitespace removed.

Syntax: lTrim(original)

Returns:

A String.

Arguments:

Name Description

original

An expression that returns a string.

Considerations:

lTrim(null) returns null
Query
RETURN lTrim('   hello')
Table 2. Result
lTrim(' hello')

"hello"

1 row

replace()

replace() returns a string in which all occurrences of a specified string in the original string have been replaced by another (specified) string.

Syntax: replace(original, search, replace)

Returns:

A String.

Arguments:

Name Description

original

An expression that returns a string.

search

An expression that specifies the string to be replaced in original.

replace

An expression that specifies the replacement string.

Considerations:

If any argument is null, null will be returned.

If search is not found in original, original will be returned.
Query
RETURN replace("hello", "l", "w")
Table 3. Result
replace("hello", "l", "w")

"hewwo"

1 row

reverse()

reverse() returns a string in which the order of all characters in the original string have been reversed.

Syntax: reverse(original)

Returns:

A String.

Arguments:

Name Description

original

An expression that returns a string.

Considerations:

reverse(null) returns null.
Query
RETURN reverse('anagram')
Table 4. Result
reverse('anagram')

"margana"

1 row

right()

right() returns a string containing the specified number of rightmost characters of the original string.

Syntax: right(original, length)

Returns:

A String.

Arguments:

Name Description

original

An expression that returns a string.

n

An expression that returns a positive integer.

Considerations:

right(null, length) and right(null, null) both return null

right(original, null) will raise an error.

If length is not a positive integer, an error is raised.

If length exceeds the size of original, original is returned.
Query
RETURN right('hello', 3)
Table 5. Result
right('hello', 3)

"llo"

1 row

rtrim()

rTrim() returns the original string with trailing whitespace removed.

Syntax: rTrim(original)

Returns:

A String.

Arguments:

Name Description

original

An expression that returns a string.

Considerations:

rTrim(null) returns null
Query
RETURN rTrim('hello   ')
Table 6. Result
rTrim('hello ')

"hello"

1 row

split()

split() returns a list of strings resulting from the splitting of the original string around matches of the given delimiter.

Syntax: split(original, splitDelimiter)

Returns:

A list of Strings.

Arguments:

Name Description

original

An expression that returns a string.

splitDelimiter

The string with which to split original.

Considerations:

split(null, splitDelimiter) and split(original, null) both return null
Query
RETURN split('one,two', ',')
Table 7. Result
split('one,two', ',')

["one","two"]

1 row

substring()

substring() returns a substring of the original string, beginning with a 0-based index start and length.

Syntax: substring(original, start [, length])

Returns:

A String.

Arguments:

Name Description

original

An expression that returns a string.

start

An expression that returns a positive integer, denoting the position at which the substring will begin.

length

An expression that returns a positive integer, denoting how many characters of original will be returned.

Considerations:

start uses a zero-based index.

If length is omitted, the function returns the substring starting at the position given by start and extending to the end of original.

If original is null, null is returned.

If either start or length is null or a negative integer, an error is raised.

If start is 0, the substring will start at the beginning of original.

If length is 0, the empty string will be returned.
Query
RETURN substring('hello', 1, 3), substring('hello', 2)
Table 8. Result
substring('hello', 1, 3) substring('hello', 2)

"ell"

"llo"

1 row

toLower()

toLower() returns the original string in lowercase.

Syntax: toLower(original)

Returns:

A String.

Arguments:

Name Description

original

An expression that returns a string.

Considerations:

toLower(null) returns null
Query
RETURN toLower('HELLO')
Table 9. Result
toLower('HELLO')

"hello"

1 row

toString()

toString() converts an integer, float or boolean value to a string.

Syntax: toString(expression)

Returns:

A String.

Arguments:

Name Description

expression

An expression that returns a number, a boolean, or a string.

Considerations:

toString(null) returns null

If expression is a string, it will be returned unchanged.
Query
RETURN toString(11.5), toString('already a string'), toString(TRUE ), toString(date({ year:1984, month:10, day:11 })) AS dateString, toString(datetime({ year:1984, month:10, day:11, hour:12, minute:31, second:14, millisecond: 341, timezone: 'Europe/Stockholm' })) AS datetimeString, toString(duration({ minutes: 12, seconds: -60 })) AS durationString
Table 10. Result
toString(11.5) toString('already a string') toString(TRUE ) dateString datetimeString durationString

"11.5"

"already a string"

"true"

"1984-10-11"

"1984-10-11T12:31:14.341+01:00[Europe/Stockholm]"

"PT11M"

1 row

toUpper()

toUpper() returns the original string in uppercase.

Syntax: toUpper(original)

Returns:

A String.

Arguments:

Name Description

original

An expression that returns a string.

Considerations:

toUpper(null) returns null
Query
RETURN toUpper('hello')
Table 11. Result
toUpper('hello')

"HELLO"

1 row

trim()

trim() returns the original string with leading and trailing whitespace removed.

Syntax: trim(original)

Returns:

A String.

Arguments:

Name Description

original

An expression that returns a string.

Considerations:

trim(null) returns null
Query
RETURN trim('   hello   ')
Table 12. Result
trim(' hello ')

"hello"

1 row