3.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 and booleans.

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

See also Section 3.2.5.6, “String operators”.

3.4.8.1. left()

left() returns a string containing the left n characters of the original string.

Syntax: left(original, length)

Arguments:

Name Description

original

An expression that returns a string

n

An expression that returns a positive number.

Query. 

RETURN left('hello', 3)

Table 3.176. Result
left('hello', 3)

1 row

"hel"

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

3.4.8.2. ltrim()

lTrim() returns the original string with whitespace removed from the left side.

Syntax: lTrim(original)

Arguments:

Name Description

original

An expression that returns a string

Query. 

RETURN lTrim('   hello')

Table 3.177. Result
lTrim(' hello')

1 row

"hello"

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

3.4.8.3. replace()

replace() returns a string with the search string replaced by the replace string. It replaces all occurrences.

Syntax: replace(original, search, replace)

Arguments:

Name Description

original

An expression that returns a string

search

An expression that returns a string to search for

replace

An expression that returns the string to replace the search string with

Query. 

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

Table 3.178. Result
replace("hello", "l", "w")

1 row

"hewwo"

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

3.4.8.4. reverse()

reverse() returns the original string reversed.

Syntax: reverse(original)

Arguments:

Name Description

original

An expression that returns a string

Query. 

RETURN reverse('anagram')

Table 3.179. Result
reverse('anagram')

1 row

"margana"

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

3.4.8.5. right()

right() returns a string containing the right n characters of the original string.

Syntax: right(original, length)

Arguments:

Name Description

original

An expression that returns a string

n

An expression that returns a positive number.

Query. 

RETURN right('hello', 3)

Table 3.180. Result
right('hello', 3)

1 row

"llo"

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

3.4.8.6. rtrim()

rTrim() returns the original string with whitespace removed from the right side.

Syntax: rTrim(original)

Arguments:

Name Description

original

An expression that returns a string

Query. 

RETURN rTrim('hello   ')

Table 3.181. Result
rTrim('hello ')

1 row

"hello"

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

3.4.8.7. split()

split() returns the sequence of strings which are delimited by split patterns.

Syntax: split(original, splitPattern)

Arguments:

Name Description

original

An expression that returns a string

splitPattern

The string to split the original string with

Query. 

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

Table 3.182. Result
split('one,two', ',')

1 row

["one","two"]

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

3.4.8.8. substring()

substring() returns a substring of the original, with a 0-based index start and length. If length is omitted, it returns a substring from start until the end of the string.

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

Arguments:

Name Description

original

An expression that returns a string

start

An expression that returns a positive number

length

An expression that returns a positive number

Query. 

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

Table 3.183. 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)

3.4.8.9. toLower()

toLower() returns the original string in lowercase.

Syntax: toLower(original)

Arguments:

Name Description

original

An expression that returns a string

Query. 

RETURN toLower('HELLO')

Table 3.184. Result
toLower('HELLO')

1 row

"hello"

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

3.4.8.10. toString()

toString() converts the argument to a string. It converts integral and floating point numbers and booleans to strings, and if called with a string will leave it unchanged.

Syntax: toString(expression)

Arguments:

Name Description

expression

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

Query. 

RETURN toString(11.5), toString('already a string'), toString(TRUE )

Table 3.185. Result
toString(11.5) toString('already a string') toString(true)

1 row

"11.5"

"already a string"

"true"

Try this query live.  none RETURN toString(11.5), toString('already a string'), toString(true)

3.4.8.11. toUpper()

toUpper() returns the original string in uppercase.

Syntax: toUpper(original)

Arguments:

Name Description

original

An expression that returns a string

Query. 

RETURN toUpper('hello')

Table 3.186. Result
toUpper('hello')

1 row

"HELLO"

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

3.4.8.12. trim()

trim() returns the original string with whitespace removed from both sides.

Syntax: trim(original)

Arguments:

Name Description

original

An expression that returns a string

Query. 

RETURN trim('   hello   ')

Table 3.187. Result
trim(' hello ')

1 row

"hello"

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