String functions

These functions all operate on string expressions only, and will return an error if used on any other values. The exception to this rule is toString(), which also accepts numbers, booleans and temporal values (i.e. Date, Time. LocalTime, DateTime, LocalDateTime or Duration values).

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.

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:

1. 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"

Rows: 1

2. 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"

Rows: 1

3. 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"

Rows: 1

4. 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"

Rows: 1

5. 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"

Rows: 1

6. 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"

Rows: 1

7. 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"]

Rows: 1

8. 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"

Rows: 1

9. 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"

Rows: 1

10. toString()

toString() converts an integer, float, boolean, string, point, duration, date, time, localtime, localdatetime or datetime value to a string.

Syntax: toString(expression)

Returns:

A String.

Arguments:

Name Description

expression

An expression that returns a number, a boolean, string, temporal or spatial value.

Considerations:

toString(null) returns null

If expression is a string, it will be returned unchanged.

This function will return an error if provided with an expression that is not an integer, float, string, boolean, point, duration, date, time, localtime, localdatetime or datetime value.
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"

Rows: 1

11. toStringOrNull()

The function toStringOrNull() converts an integer, float, boolean, string, point, duration, date, time, localtime, localdatetime or datetime value to a string.

Syntax: toStringOrNull(expression)

Returns:

A String or null.

Arguments:

Name Description

expression

Any expression that returns a value.

Considerations:

toStringOrNull(null) returns null.

If the expression is not an integer, float, string, boolean, point, duration, date, time, localtime, localdatetime or datetime value, null will be returned.
Query
RETURN toStringOrNull(11.5),
toStringOrNull('already a string'),
toStringOrNull(true),
toStringOrNull(date({year:1984, month:10, day:11})) AS dateString,
toStringOrNull(datetime({year:1984, month:10, day:11, hour:12, minute:31, second:14, millisecond: 341, timezone: 'Europe/Stockholm'})) AS datetimeString,
toStringOrNull(duration({minutes: 12, seconds: -60})) AS durationString,
toStringOrNull(['A', 'B', 'C']) AS list
Table 11. Result
toStringOrNull(11.5) toStringOrNull('already a string') toStringOrNull(true) dateString datetimeString durationString list

"11.5"

"already a string"

"true"

"1984-10-11"

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

"PT11M"

<null>

Rows: 1

12. 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 12. Result
toUpper('hello')

"HELLO"

Rows: 1

13. 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 13. Result
trim(' hello ')

"hello"

Rows: 1