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.

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

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:

A 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 original string have been replaced by another (specified) string.

Syntax:

replace(original, search, replace)

Returns:

A 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 original string have been reversed.

Syntax:

reverse(original)

Returns:

A 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 of the original string.

Syntax:

right(original, length)

Returns:

A 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 original string with trailing whitespace removed.

Syntax:

rtrim(original)

Returns:

A 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 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

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 original string, beginning with a zero-based index start and length.

Syntax:

substring(original, start [, length])

Returns:

A 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 original string in lowercase.

Syntax:

toLower(original)

Returns:

A 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, time, localtime, localdatetime, or datetime value to a string.

Syntax:

toString(expression)

Returns:

A String.

Arguments:

Name Description

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

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.

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, time, localtime, localdatetime, or datetime value to a string.

Syntax:

toStringOrNull(expression)

Returns:

A 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, string, boolean, point, duration, date, time, localtime, localdatetime, or 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 original string in uppercase.

Syntax:

toUpper(original)

Returns:

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

Example 13. trim()

Query

RETURN trim('   hello   ')

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

Was this page helpful?