right

Applies to VAST Cluster 5.5.

Functions › Text basics › Parts of a string. Returns the last n characters of a string; with a negative n, everything except the first -n characters. There are no aliases.

Syntax

right(s, n)

Returns: VARCHAR.

Aliases: none.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_right. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), 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 four products. Each SKU is a region, a country or state code and a five-digit serial number; the last row has no values:

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_right"'

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, "<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}.products"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, sku VARCHAR, name VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'EU-FR-00042', 'Café crème'), "
    "(2, 'US-NY-00107', 'Bagel'), "
    "(3, 'JP-13-00300', 'Matcha latte'), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

right counts in Unicode code points from the end of the string:

A code point isn't always what a reader sees as one character. A letter followed by a combining accent (e and U+0301, displayed as é) is two code points, and the accent is the last one: right('xe\u0301', 1) returns the accent alone, without its letter. A precomposed é (U+00E9) is one code point, so right('café', 2) returns 'fé'. To count what a reader sees, use right_grapheme.

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples. The last value of the first line is the lone accent, which your terminal may draw on the quote before it:

run("SELECT right(NULL, 2), right('abc', NULL), right('', 2), right('abc', 0), right('abc', -1), "
    "right('abc', 10), right('xe\u0301', 1)")
run("SELECT right(123, 2)")

[(None, None, '', '', 'bc', 'abc', '́')]
InternalError: Binder Error: No function matches the given name and argument types 'right(INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The serial number (the last five characters of the SKU) and the last five characters of the name. crème keeps its è, because a precomposed è is one code point:

run(f"SELECT id, right(sku, 5), right(name, 5) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '00042', 'crème'), (2, '00107', 'Bagel'), (3, '00300', 'latte'), (4, None, None)]

Drop the region and its dash, the first three characters, with a negative count:

run(f"SELECT id, right(sku, -3) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'FR-00042'), (2, 'NY-00107'), (3, '13-00300'), (4, None)]

Find a product by its serial number:

run(f"SELECT id, sku FROM {TABLE} WHERE right(sku, 5) = '00107' ORDER BY id LIMIT 10")

[(2, 'US-NY-00107')]

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 right() is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes right() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes right() and keeps the rows that match. No rows are skipped in storage.

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE right(sku, 5) = '00107'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│("right"(sku, 5) = '00107')│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            sku            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• A suffix test isn't pushed down either. sku LIKE '%00107' is rewritten to suffix(sku, '00107'), and suffix() runs in a FILTER step, like right(sku, 5) = '00107'. Use whichever reads best; see ends_with for the suffix tests:

plan(f"SELECT id FROM {TABLE} WHERE sku LIKE '%00107'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│    suffix(sku, '00107')   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            sku            │
│             id            │
│                           │
└───────────────────────────┘

• Store the suffix if you filter on it often. Compute it when you write the rows and keep it in its own column: serial = '00107' is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan for a raw column compared with a constant has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE sku = 'US-NY-00107'")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Combine it with a pushed-down condition. In id > 100 AND right(sku, 5) = '00107', id > 100 is still evaluated in VAST DataBase, so right() runs only on the rows that pass it.

Limitations and errors

• VARCHAR only. A number, a date, a boolean, a BLOB or a list fails with No function matches the given name and argument types. Cast a value to text first: right(CAST(n AS VARCHAR), 2). For the last elements of a list, use list_slice.
• The count must be an integer. A DECIMAL such as 2.7 fails with No function matches the given name and argument types 'right(STRING_LITERAL, DECIMAL(2,1))'; it isn't converted for you. Cast it to an integer first: right(s, CAST(x AS BIGINT)). The cast rounds, it doesn't truncate.
• Code points, not characters. right can return a combining accent without its letter. See What it does.

run("SELECT right(123, 2)")
run("SELECT right(CAST(123 AS VARCHAR), 2)")

InternalError: Binder Error: No function matches the given name and argument types 'right(INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.
[('23',)]

Similar functions

On 'cafe\u0301' (café with a combining accent) and 1, right returns the accent alone and right_grapheme returns 'e\u0301' (é):

run("SELECT right('abcdef', 2), substring('abcdef', -2), right_grapheme('abcdef', 2), left('abcdef', 2)")
run("SELECT right('cafe\u0301', 1), right_grapheme('cafe\u0301', 1)")

[('ef', 'ef', 'ef', 'ab')]
[('́', 'é')]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Text basics: all the text functions.
• left, substring and right_grapheme.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.