--- title: "reverse" slug: "dev-database-reference-sql-functions-string-reverse" updated: 2026-10-30T18:18:56Z published: 2026-10-30T18:18:56Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-reverse" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # reverse *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) â€ē [Text basics](/develop/docs/dev-database-reference-sql-functions-string-text-basics) â€ē Changing text.* Returns a string with its characters in reverse order. A letter with a combining accent and a flag emoji stay whole. There are no aliases. ## Syntax ``` reverse(s) ``` | Argument | Type | Description | | --- | --- | --- | | `s` | `VARCHAR` | The string to reverse. | **Returns:** `VARCHAR`. **Aliases:** none. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_reverse`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see. This block connects, defines the `run()` and `plan()` helpers, and creates a table of five words. `été` is written with combining accents, and the last row has no value: ``` import os import re import adbc_driver_manager import adbc_driver_manager.dbapi as dbapi import adbc_driver_vastdb BUCKET = os.environ["VASTDB_BUCKET"] SCHEMA = f'"{BUCKET}/docs_sql_fn_reverse"' conn = dbapi.connect( driver=adbc_driver_vastdb.get_driver_path(), db_kwargs={ "vast.db.endpoint": os.environ["QE_ENDPOINT"], "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"], "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"], }, autocommit=True, ) def server_message(e): """The server's message in a driver error, without the request ID or the echoed statement.""" text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e)) found = (re.search(r'message: "(.*?)", request_id', text, re.S) or re.search(r"description: (.*)\)", text, re.S)) message = found.group(1) if found else text.strip().splitlines()[-1] message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S) message = re.split(r"\\n|\n", message)[0].replace('\\"', '"') return message.replace(BUCKET, "") def run(sql): """Run one statement. Print the rows it returns, or the error.""" try: with conn.cursor() as cur: cur.execute(sql) rows = cur.fetchall() if cur.description else [] except (adbc_driver_manager.Error, OSError) as e: print(f"{type(e).__name__}: {server_message(e)}") else: print(rows) def plan(sql): """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert.""" with conn.cursor() as cur: cur.execute("EXPLAIN " + sql) steps = cur.fetchall()[0][1] print("\n".join(line for line in steps.splitlines() if "Rows" not in line)) TABLE = f"{SCHEMA}.words" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, word VARCHAR)") run(f"INSERT INTO {TABLE} VALUES " "(1, 'level'), " "(2, 'Hello'), " "(3, 'e\u0301te\u0301'), " "(4, 'kayak'), " "(5, NULL)") ``` ``` [] [] [(5,)] ``` ## What it does `reverse` reverses the order of the characters as a reader sees them (grapheme clusters), not of the code points they're made of: | Input | Result | What stays together | | --- | --- | --- | | `'Hello'` | `'olleH'` | | | `'café'` (precomposed `é`, one code point) | `'éfac'` | | | `'e\u0301x'` (e, a combining accent, x) | `'xe\u0301'` (xÊ) | the accent stays after its `e` | | `'🇫🇷'` (a flag: two regional-indicator code points) | `'🇫🇷'` | the flag isn't turned into 🇷đŸ‡Ģ | | `'e\u0301 🇫🇷 ẞ'` | `'ẞ 🇫🇷 e\u0301'` | both | | A family emoji (man, woman, girl, joined by zero-width joiners) | the same family emoji | the whole sequence | | `'\r\n'` (carriage return, line feed) | `'\n\r'` | nothing: CR and LF are two grapheme clusters | This is different from [left](/develop/docs/dev-database-reference-sql-functions-string-left), [right](/develop/docs/dev-database-reference-sql-functions-string-right) and [substring](/develop/docs/dev-database-reference-sql-functions-string-substring), which count code points and can split a letter from its accent. ## NULLs and edge cases | Input | Result | | --- | --- | | `NULL` | `NULL` | | `''` (empty string) | `''` | | `'abc'` | `'cba'` | | `'e\u0301x'` | `'xe\u0301'`, with the accent at code point 3 | | `'🇫🇷'` | `'🇫🇷'`, equal to the input | | A number, date, boolean, `BLOB` or list | Error: `No function matches the given name and argument types` | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-string-reverse#set-up-the-examples). `strpos()` of the accent shows it is still the last code point, after the `e`: ``` run("SELECT reverse(NULL), reverse(''), reverse('abc'), reverse('e\u0301x'), strpos(reverse('e\u0301x'), '\u0301')") run("SELECT reverse('\U0001F1EB\U0001F1F7'), reverse('\U0001F1EB\U0001F1F7') = '\U0001F1EB\U0001F1F7'") run("SELECT reverse(123)") run("SELECT reverse(['a', 'b'])") ``` ``` [(None, '', 'cba', 'xÊ', 3)] [('đŸ‡Ģ🇷', True)] InternalError: Binder Error: No function matches the given name and argument types 'reverse(INTEGER_LITERAL)'. You might need to add explicit type casts. InternalError: Binder Error: No function matches the given name and argument types 'reverse(VARCHAR[])'. You might need to add explicit type casts. ``` ## Examples Each word reversed: ``` run(f"SELECT id, word, reverse(word) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 'level', 'level'), (2, 'Hello', 'olleH'), (3, 'ÊtÊ', 'ÊtÊ'), (4, 'kayak', 'kayak'), (5, None, None)] ``` The palindromes: the words that read the same backwards. `été` is one, because its accents stay after their letters when it's reversed: ``` run(f"SELECT id, word FROM {TABLE} WHERE word = reverse(word) ORDER BY id LIMIT 10") ``` ``` [(1, 'level'), (3, 'ÊtÊ'), (4, 'kayak')] ``` Find a word from its reversed spelling, with `reverse()` on the constant: ``` run(f"SELECT id, word FROM {TABLE} WHERE word = reverse('olleH') ORDER BY id LIMIT 10") ``` ``` [(2, 'Hello')] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so `reverse()` of a column is always computed by the Query Engine: - **In the `SELECT` list,** the scan reads the column and the Query Engine computes `reverse()` for each row it returns. - **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step in the Query Engine computes `reverse()` and keeps the rows that match. No rows are skipped in storage. The plan of the palindrome example shows the `FILTER` step above the scan: ``` plan(f"SELECT id FROM {TABLE} WHERE word = reverse(word)") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────â”Ŧ─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (word = reverse(word)) │ │ │ └─────────────â”Ŧ─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ word │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Reverse the constant, not the column.** `reverse(word) = 'olleH'` computes `reverse()` for every row in a `FILTER` step. `word = reverse('olleH')` asks the same question, but `reverse()` runs once, on the constant, and the comparison of the raw column with the result is pushed down to VAST DataBase. The plan has no `FILTER` step: ``` plan(f"SELECT id FROM {TABLE} WHERE word = reverse('olleH')") ``` ``` ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` - **Store the result if you filter on it often.** A test that needs `reverse()` of the column, such as the palindrome test, can't be pushed down. Compute it when you write the rows and keep it in its own column, such as `is_palindrome`: `is_palindrome = true` is pushed down. - **Combine it with a pushed-down condition.** In `id > 100 AND word = reverse(word)`, `id > 100` is still evaluated in VAST DataBase, so `reverse()` runs only on the rows that pass it. ## Limitations and errors - **`VARCHAR` only.** A number, a date, a boolean or a `BLOB` fails with `No function matches the given name and argument types`. Cast a value to text first: `reverse(CAST(n AS VARCHAR))`. - **Not for lists.** `reverse(['a', 'b'])` fails with `No function matches the given name and argument types 'reverse(VARCHAR[])'`. Use [list_reverse](/develop/docs/dev-database-reference-sql-functions-lists-list-reverse). - **No `strrev`.** `strrev('abc')` fails with `Scalar Function with name strrev does not exist!`. Use `reverse`. ``` run("SELECT reverse(123)") run("SELECT reverse(CAST(123 AS VARCHAR)), list_reverse(['a', 'b'])") ``` ``` InternalError: Binder Error: No function matches the given name and argument types 'reverse(INTEGER_LITERAL)'. You might need to add explicit type casts. [('321', ['b', 'a'])] ``` ## Similar functions | Function | Reverses | Result for `'e\u0301x'` (Êx) | | --- | --- | --- | | `reverse` | the grapheme clusters of a string | `'xe\u0301'` (xÊ) | | [list_reverse](/develop/docs/dev-database-reference-sql-functions-lists-list-reverse) | the elements of a list | (lists only; `[1, 2, 3]` gives `[3, 2, 1]`) | ``` run("SELECT reverse('e\u0301x'), list_reverse([1, 2, 3])") ``` ``` [('xÊ', [3, 2, 1])] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Text basics](/develop/docs/dev-database-reference-sql-functions-string-text-basics): all the text functions. - [list_reverse](/develop/docs/dev-database-reference-sql-functions-lists-list-reverse): reverse a list. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.