parse_filename

Applies to VAST Cluster 5.5.

Functions › Text basics › Paths. Returns the last component of a file path, the file name, optionally without its last extension. It has no aliases.

Syntax

parse_filename(path)
parse_filename(path, trim_extension)
parse_filename(path, separator)
parse_filename(path, trim_extension, separator)

Returns: VARCHAR.

Aliases: none.

Argument order. trim_extension always comes before separator. With two arguments, the type of the second one decides what it is: a BOOLEAN is trim_extension, a VARCHAR is separator. A quoted 'true' is a VARCHAR, so parse_filename('a/b.txt', 'true') reads it as an (unknown) separator and returns 'b.txt' with the extension kept: write true without quotes. To pass both, write parse_filename(path, true, 'backslash'); the order parse_filename(path, 'backslash', true) fails with No function matches the given name and argument types.

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_parse_filename. 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 five file paths, with the file name stored next to each path. One path is a Windows path and one ends with a separator; 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_parse_filename"'

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}.files"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, path VARCHAR, file_name VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '/data/logs/app.log', 'app.log'), "
    "(2, '/home/ada/projects/notes.tar.gz', 'notes.tar.gz'), "
    "(3, 'C:\\Users\\jose\\poems.docx', 'poems.docx'), "
    "(4, 'exports/2026/', ''), "
    "(5, NULL, NULL)")

[]
[]
[(5,)]

What it does

parse_filename returns what comes after the last separator of the path. A path that ends with a separator has nothing after it, and gives an empty string:

trim_extension. With true, the file name loses its last extension: the last . of the file name and everything after it. Only one extension goes, a dot in a directory name doesn't count, and a name whose only . is its first character, such as .bashrc, is all extension:

separator. With 'forward_slash', only / separates components, so a backslash stays in the name; 'system' means / on the cluster and does the same. With 'backslash', only \ does. 'both_slash', NULL and a value that isn't a mode name split on both, as the default does:

In Python code, write each \ of a path as \\ inside the string, so that the SQL receives one backslash. Python prints each backslash in the results doubled: 'a\\b.txt' is the string a\b.txt.

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run("SELECT parse_filename(NULL), parse_filename(NULL, true), parse_filename('a/b.txt', NULL), parse_filename('')")
run("SELECT parse_filename('a/b/'), parse_filename('c'), parse_filename('c', true), parse_filename('.bashrc', true)")
run("SELECT parse_filename('a/b/c.tar.gz', true), parse_filename('a/b/c.tar.gz', false), parse_filename('a/b/c.txt', 'x')")
run("SELECT parse_filename('a\\b.txt', 'forward_slash'), parse_filename('a\\b.txt', true, 'backslash')")
run("SELECT parse_filename(123)")

[(None, None, 'b.txt', '')]
[('', 'c', 'c', '')]
[('c.tar', 'c.tar.gz', 'c.txt')]
[('a\\b.txt', 'b')]
InternalError: Binder Error: No function matches the given name and argument types 'parse_filename(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The file name of each path, with and without its last extension. The path that ends with a separator has no file name:

run(f"SELECT id, parse_filename(path), parse_filename(path, true) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'app.log', 'app'), (2, 'notes.tar.gz', 'notes.tar'), (3, 'poems.docx', 'poems'), (4, '', ''), (5, None, None)]

The same paths read with / as the only separator, then with \ as the only separator and the extension removed. With 'backslash', a path with no backslash is a single component, so the whole path comes back, less its extension:

run(f"SELECT id, parse_filename(path, 'forward_slash'), parse_filename(path, true, 'backslash') "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'app.log', '/data/logs/app'), (2, 'notes.tar.gz', '/home/ada/projects/notes.tar'), (3, 'C:\\Users\\jose\\poems.docx', 'poems'), (4, '', 'exports/2026/'), (5, None, None)]

Find a file by name, wherever it is:

run(f"SELECT id, path FROM {TABLE} WHERE parse_filename(path) = 'app.log' ORDER BY id LIMIT 10")

[(1, '/data/logs/app.log')]

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

• In the SELECT list, the scan reads the column and the Query Engine computes parse_filename() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes parse_filename() 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 parse_filename(path) = 'app.log'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (parse_filename(path) =  │
│         'app.log')        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the file name if you look files up by name. Compute it when you write the rows and keep it in its own column. A comparison of a raw column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan for file_name = 'app.log' has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE file_name = 'app.log'")

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

• A suffix pattern on the raw path doesn't help. path LIKE '%/app.log' isn't pushed down either: its plan has a FILTER step too. Store the file name instead.

plan(f"SELECT id FROM {TABLE} WHERE path LIKE '%/app.log'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  suffix(path, '/app.log') │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘

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

Limitations and errors

• Strings only. A number, a decimal, a date, a boolean, a BLOB or a list as path fails with No function matches the given name and argument types.
• trim_extension before separator. The four forms are the ones in Syntax; there's no form that takes the separator first and trim_extension after it.
• Only the last extension. parse_filename('a/b/c.tar.gz', true) returns 'c.tar', not 'c'.
• A trailing separator gives ''. parse_filename('a/b/') returns '', while parse_path drops the trailing separator and ends its list with 'b'.
• Unknown separator names aren't errors. A value other than the mode names is accepted and splits on / and \. Check the spelling, and don't quote true: 'true' is read as a separator name.

run("SELECT parse_filename('\\xAA'::BLOB)")
run("SELECT parse_filename('a/b.txt', 'forward_slash', true)")

InternalError: Binder Error: No function matches the given name and argument types 'parse_filename(BLOB)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'parse_filename(STRING_LITERAL, STRING_LITERAL, BOOLEAN)'. You might need to add explicit type casts.

Similar functions

run("SELECT parse_filename('/home/ada/notes.txt'), parse_filename('/home/ada/notes.txt', true), "
    "parse_dirpath('/home/ada/notes.txt'), parse_dirname('/home/ada/notes.txt'), parse_path('/home/ada/notes.txt')")
run("SELECT parse_filename('C:\\dir\\file.tar.gz'), parse_filename('C:\\dir\\file.tar.gz', true), "
    "parse_dirpath('C:\\dir\\file.tar.gz'), parse_dirname('C:\\dir\\file.tar.gz'), parse_path('C:\\dir\\file.tar.gz')")

[('notes.txt', 'notes', '/home/ada', '/', ['/', 'home', 'ada', 'notes.txt'])]
[('file.tar.gz', 'file.tar', 'C:\\dir', 'C:', ['C:', 'dir', 'file.tar.gz'])]

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.
• parse_path: the separator modes in detail.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.