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, ZONED TIME` LOCAL TIME, ZONED DATETIME, LOCAL DATETIME 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.

left()

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

Syntax:

left(original, length)

Returns:

STRING

Arguments:

Name Description

Name	Description
`original`	An expression that returns a `STRING`.
`length`	An expression that returns a positive `INTEGER`.

original

An expression that returns a STRING.

length

An expression that returns a positive INTEGER.

Considerations:

left(null, length) return null.

left(null, null) 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.

Example 1. left()

Query

RETURN left('hello', 3)

Table 1. Result
left('hello', 3)
`"hel"`
Rows: 1

ltrim()

ltrim() returns the original STRING with leading whitespace removed.

Syntax:

ltrim(original)

Returns:

STRING

Arguments:

Name Description

Name	Description
`original`	An expression that returns a `STRING`.

original

An expression that returns a STRING.

Considerations:

ltrim(null) returns null.

Example 2. ltrim()

Query

RETURN ltrim('   hello')

Table 2. Result
ltrim(' hello')
`"hello"`
Rows: 1

replace()

replace() returns a STRING in which all occurrences of a specified STRING in the given STRING have been replaced by another (specified) replacement STRING.

Syntax:

replace(original, search, replace)

Returns:

STRING

Arguments:

Name Description

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`.

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.

Example 3. replace()

Query

RETURN replace("hello", "l", "w")

Table 3. Result
replace("hello", "l", "w")
`"hewwo"`
Rows: 1

reverse()

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

Syntax:

reverse(original)

Returns:

STRING

Arguments:

Name Description

Name	Description
`original`	An expression that returns a `STRING`.

original

An expression that returns a STRING.

Considerations:

reverse(null) returns null.

Example 4. reverse

Query

RETURN reverse('anagram')

Table 4. Result
reverse('anagram')
`"margana"`
Rows: 1

right()

right() returns a STRING containing the specified number of rightmost characters in the given STRING.

Syntax:

right(original, length)

Returns:

STRING

Arguments:

Name Description

Name	Description
`original`	An expression that returns a `STRING`.
`length`	An expression that returns a positive `INTEGER`.

original

An expression that returns a STRING.

length

An expression that returns a positive INTEGER.

Considerations:

right(null, length) return null.

right(null, null) 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.

Example 5. right()

Query

RETURN right('hello', 3)

Table 5. Result
right('hello', 3)
`"llo"`
Rows: 1

rtrim()

rtrim() returns the given STRING with trailing whitespace removed.

Syntax:

rtrim(original)

Returns:

STRING

Arguments:

Name Description

Name	Description
`original`	An expression that returns a `STRING`.

original

An expression that returns a STRING.

Considerations:

rtrim(null) returns null.

Example 6. rtrim()

Query

RETURN rtrim('hello   ')

Table 6. Result
rtrim('hello ')
`"hello"`
Rows: 1

split()

split() returns a LIST<STRING> resulting from the splitting of the given STRING around matches of the given delimiter.

Syntax:

split(original, splitDelimiter)

Returns:

LIST<STRING>

Arguments:

Name Description

Name	Description
`original`	An expression that returns a `STRING`.
`splitDelimiter`	The `STRING` with which to split `original`.

original

An expression that returns a STRING.

splitDelimiter

The STRING with which to split original.

Considerations:

split(null, splitDelimiter) return null.

split(original, null) return null

Example 7. split()

Query

RETURN split('one,two', ',')

Table 7. Result
split('one,two', ',')
`["one","two"]`
Rows: 1

substring()

substring() returns a substring of the given STRING, beginning with a zero-based index start and length.

Syntax:

substring(original, start [, length])

Returns:

STRING

Arguments:

Name Description

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.

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.

Example 8. substring()

Query

RETURN substring('hello', 1, 3), substring('hello', 2)

Table 8. Result
substring('hello', 1, 3)	substring('hello', 2)
`"ell"`	`"llo"`
Rows: 1

toLower()

toLower() returns the given STRING in lowercase.

Syntax:

toLower(original)

Returns:

STRING

Arguments:

Name Description

Name	Description
`original`	An expression that returns a `STRING`.

original

An expression that returns a STRING.

Considerations:

toLower(null) returns null.

Example 9. toLower()

Query

RETURN toLower('HELLO')

Table 9. Result
toLower('HELLO')
`"hello"`
Rows: 1

toString()

toString() converts an INTEGER, FLOAT, BOOLEAN, STRING, POINT, DURATION, DATE, ZONED TIME, LOCAL TIME, LOCAL DATETIME or ZONED DATETIME value to a STRING.

Syntax:

toString(expression)

Returns:

STRING

Arguments:

Name Description

Name	Description
`expression`	An expression that returns an `INTEGER`, `FLOAT`, `BOOLEAN`, `STRING`, `POINT`, `DURATION`, `DATE`, `ZONED TIME`, `LOCAL TIME`, `LOCAL DATETIME` or `ZONED DATETIME` value.

expression

An expression that returns an INTEGER, FLOAT, BOOLEAN, STRING, POINT, DURATION, DATE, ZONED TIME, LOCAL TIME, LOCAL DATETIME or ZONED DATETIME 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, BOOLEAN, STRING, POINT, DURATION, DATE, ZONED TIME, LOCAL TIME, LOCAL DATETIME or ZONED DATETIME value.

Example 10. toString()

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

toStringOrNull()

The function toStringOrNull() converts an INTEGER, FLOAT, BOOLEAN, STRING, POINT, DURATION, DATE, ZONED TIME, LOCAL TIME, LOCAL DATETIME or ZONED DATETIME value to a STRING.

Syntax:

toStringOrNull(expression)

Returns:

STRING or null.

Arguments:

Name Description

Name	Description
`expression`	Any expression that returns a value.

expression

Any expression that returns a value.

Considerations:

toStringOrNull(null) returns null.

If the expression is not an INTEGER, FLOAT, BOOLEAN, STRING, POINT, DURATION, DATE, ZONED TIME, LOCAL TIME, LOCAL DATETIME or ZONED DATETIME value, null will be returned.

Example 11. toStringOrNull()

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

toUpper()

toUpper() returns the given STRING in uppercase.

Syntax:

toUpper(original)

Returns:

STRING

Arguments:

Name Description

Name	Description
`original`	An expression that returns a `STRING`.

original

An expression that returns a STRING.

Considerations:

toUpper(null) returns null.

Example 12. toUpper()

Query

RETURN toUpper('hello')

Table 12. Result
toUpper('hello')
`"HELLO"`
Rows: 1

trim()

trim() returns the given STRING with leading and trailing whitespace removed.

Syntax:

trim(original)

Returns:

STRING

Arguments:

Name Description

original

An expression that returns a STRING.

Considerations:

trim(null) returns null.

Example 13. trim()

Query

RETURN trim('   hello   ')

Table 13. Result
trim(' hello ')
`"hello"`
Rows: 1

Was this page helpful?