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

See also Section 3.2.5.4, “String operators”.

Figure 3.21. Graph
alt

3.4.5.1. replace()

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

Syntax: replace( original, search, replace )

Arguments:

  • 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")

Result. 

+----------------------------+
| replace("hello", "l", "w") |
+----------------------------+
| "hewwo"                    |
+----------------------------+
1 row

3.4.5.2. 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:

  • 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)

Result. 

+--------------------------------------------------+
| substring('hello', 1, 3) | substring('hello', 2) |
+--------------------------------------------------+
| "ell"                    | "llo"                 |
+--------------------------------------------------+
1 row

3.4.5.3. left()

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

Syntax: left( original, length )

Arguments:

  • original: An expression that returns a string
  • n: An expression that returns a positive number

Query. 

RETURN left('hello', 3)

Result. 

+------------------+
| left('hello', 3) |
+------------------+
| "hel"            |
+------------------+
1 row

3.4.5.4. right()

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

Syntax: right( original, length )

Arguments:

  • original: An expression that returns a string
  • n: An expression that returns a positive number

Query. 

RETURN right('hello', 3)

Result. 

+-------------------+
| right('hello', 3) |
+-------------------+
| "llo"             |
+-------------------+
1 row

3.4.5.5. ltrim()

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

Syntax: ltrim( original )

Arguments:

  • original: An expression that returns a string

Query. 

RETURN ltrim('   hello')

Result. 

+-------------------+
| ltrim('   hello') |
+-------------------+
| "hello"           |
+-------------------+
1 row

3.4.5.6. rtrim()

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

Syntax: rtrim( original )

Arguments:

  • original: An expression that returns a string

Query. 

RETURN rtrim('hello   ')

Result. 

+-------------------+
| rtrim('hello   ') |
+-------------------+
| "hello"           |
+-------------------+
1 row

3.4.5.7. trim()

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

Syntax: trim( original )

Arguments:

  • original: An expression that returns a string

Query. 

RETURN trim('   hello   ')

Result. 

+---------------------+
| trim('   hello   ') |
+---------------------+
| "hello"             |
+---------------------+
1 row

3.4.5.8. lower()

lower() returns the original string in lowercase.

Syntax: lower( original )

Arguments:

  • original: An expression that returns a string

Query. 

RETURN lower('HELLO')

Result. 

+----------------+
| lower('HELLO') |
+----------------+
| "hello"        |
+----------------+
1 row

3.4.5.9. upper()

upper() returns the original string in uppercase.

Syntax: upper( original )

Arguments:

  • original: An expression that returns a string

Query. 

RETURN upper('hello')

Result. 

+----------------+
| upper('hello') |
+----------------+
| "HELLO"        |
+----------------+
1 row

3.4.5.10. split()

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

Syntax: split( original, splitPattern )

Arguments:

  • original: An expression that returns a string
  • splitPattern: The string to split the original string with

Query. 

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

Result. 

+-----------------------+
| split('one,two', ',') |
+-----------------------+
| ["one","two"]         |
+-----------------------+
1 row

3.4.5.11. reverse()

reverse() returns the original string reversed.

Syntax: reverse( original )

Arguments:

  • original: An expression that returns a string

Query. 

RETURN reverse('anagram')

Result. 

+--------------------+
| reverse('anagram') |
+--------------------+
| "margana"          |
+--------------------+
1 row

3.4.5.12. 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:

  • expression: An expression that returns a number, a boolean, or a string

Query. 

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

Result. 

+----------------------------------------------------------------+
| toString(11.5) | toString('already a string') | toString(true) |
+----------------------------------------------------------------+
| "11.5"         | "already a string"           | "true"         |
+----------------------------------------------------------------+
1 row