4.8. 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, size(s), where s is a character in the Chinese alphabet, will return 1.

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 Section 2.7.7, “String operators”.

Functions:

4.8.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 4.78. Result
left('hello', 3)

1 row

"hel"

Try this query live.  none RETURN left('hello', 3)

4.8.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 4.79. Result
lTrim(' hello')

1 row

"hello"

Try this query live.  none RETURN lTrim(' hello')

4.8.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 4.80. Result
replace("hello", "l", "w")

1 row

"hewwo"

Try this query live.  none RETURN replace("hello", "l", "w")

4.8.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.81. Result
reverse('anagram')

1 row

"margana"

Try this query live.  none RETURN reverse('anagram')

4.8.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 4.82. Result
right('hello', 3)

1 row

"llo"

Try this query live.  none RETURN right('hello', 3)

4.8.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 4.83. Result
rTrim('hello ')

1 row

"hello"

Try this query live.  none RETURN rTrim('hello ')

4.8.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 4.84. Result
split('one,two', ',')

1 row

["one","two"]

Try this query live.  none RETURN split('one,two', ',')

4.8.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 4.85. Result
substring('hello', 1, 3) substring('hello', 2)

1 row

"ell"

"llo"

Try this query live.  none RETURN substring('hello', 1, 3), substring('hello', 2)

4.8.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 4.86. Result
toLower('HELLO')

1 row

"hello"

Try this query live.  none RETURN toLower('HELLO')

4.8.10. 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 4.87. Result
toString(11.5) toString('already a string') toString(TRUE ) dateString datetimeString durationString

1 row

"11.5"

"already a string"

"true"

"1984-10-11"

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

"PT11M"

Try this query live.  none 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

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

1 row

"HELLO"

Try this query live.  none RETURN toUpper('hello')

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

1 row

"hello"

Try this query live.  none RETURN trim(' hello ')