replace

Applies to VAST Cluster 5.5.

Functions › Text basics › Changing text. Replaces every occurrence of one substring with another. The match is exact and case-sensitive, with no patterns. replace has no aliases.

Syntax

replace(s, source, target)

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_replace. 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 files: two Linux paths, a Windows path and a row with 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_replace"'

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, title VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '/home/ada/notes.txt', 'Notes on the Analytical Engine'), "
    "(2, '/home/grace/compiler.tar.gz', 'The A-0 compiler'), "
    "(3, 'C:\\Users\\jose\\poems.docx', 'Versos sencillos'), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

replace scans s from left to right and replaces each occurrence of source with target. After a match, the scan continues after the matched text, so matches don't overlap: in 'aaa', 'aa' matches once, at the start, and the last a is left as it is.

The match compares code points, not what the text looks like. é can be stored as one code point (U+00E9) or as e followed by U+0301, and the two don't match each other: replacing a precomposed é in 'cafe' followed by U+0301 returns the string unchanged. If your data mixes the two forms, normalise both sides first with nfc_normalize.

To replace a pattern rather than a fixed substring, use regexp_replace. To replace single characters with other single characters, use translate.

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 replace(NULL, 'l', 'L'), replace('abc', NULL, 'x'), replace('abc', 'a', NULL), replace('', 'l', 'L')")
run("SELECT replace('aaa', 'aa', 'b'), replace('abc', '', 'x'), replace('aaa', 'a', ''), replace('Abc', 'a', 'x'), replace('a.b.c', '.', '/'), replace('café', 'é', 'e')")
run("SELECT replace(123, 'l', 'L')")
run("SELECT replace('\\xAA'::BLOB, 'l', 'L')")

[(None, None, None, '')]
[('ba', 'abc', '', 'Abc', 'a/b/c', 'cafe')]
InternalError: Binder Error: No function matches the given name and argument types 'replace(INTEGER_LITERAL, STRING_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'replace(BLOB, STRING_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Move the files from /home/ to /users/. The Windows path has no /home/, so it comes back unchanged:

run(f"SELECT id, replace(path, '/home/', '/users/') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '/users/ada/notes.txt'), (2, '/users/grace/compiler.tar.gz'), (3, 'C:\\Users\\jose\\poems.docx'), (4, None)]

Nest calls to make several replacements. This turns each backslash into a slash, and each title into a lowercase slug with hyphens for spaces. In the Python string, '\\' is one backslash in SQL:

run(f"SELECT id, replace(path, '\\', '/'), lower(replace(title, ' ', '-')) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '/home/ada/notes.txt', 'notes-on-the-analytical-engine'), (2, '/home/grace/compiler.tar.gz', 'the-a-0-compiler'), (3, 'C:/Users/jose/poems.docx', 'versos-sencillos'), (4, None, None)]

Find a file by its path with slashes, whichever separator it was stored with:

run(f"SELECT id, path FROM {TABLE} WHERE replace(path, '\\', '/') = 'C:/Users/jose/poems.docx' ORDER BY id LIMIT 10")

[(3, 'C:\\Users\\jose\\poems.docx')]

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

• In the SELECT list, the scan reads the column and the Query Engine computes replace() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes replace() 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 replace(path, '\\', '/') = 'C:/Users/jose/poems.docx'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ("replace"(path, '\', '/')│
│   = 'C:/Users/jose/poems  │
│          .docx')          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the normalised value if you filter on it often. Apply replace() when you write the rows and keep the result in its own column. A comparison of the raw column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan for path = '/home/ada/notes.txt' has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE path = '/home/ada/notes.txt'")

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

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

Limitations and errors

• Strings only. A number, a decimal, a BLOB, a date, a boolean or a list fails with No function matches the given name and argument types. Cast a value to VARCHAR first: replace(CAST(n AS VARCHAR), '2', '-').
• No patterns. Every character in source is matched literally. Use regexp_replace for patterns.
• Case-sensitive. replace('Abc', 'a', 'x') changes nothing.
• No match across normalisation forms. A precomposed é doesn't match e followed by U+0301. Normalise with nfc_normalize first if your data mixes them.
• NULL in any argument gives NULL, including a NULL source or target.

run("SELECT replace(DATE '2026-10-16', '-', '/')")
run("SELECT replace(CAST(123 AS VARCHAR), '2', '-')")

InternalError: Binder Error: No function matches the given name and argument types 'replace(DATE, STRING_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
[('1-3',)]

Similar functions

The first three calls return the same string for different reasons; the last two show the difference between a substring and a set of characters. reverse and repeat are the other functions that change text.

run("SELECT replace('abcabc', 'ab', 'x'), translate('abcabc', 'ab', 'x'), regexp_replace('abcabc', 'ab', 'x', 'g')")
run("SELECT replace('aaa', 'aa', 'b'), translate('aaa', 'aa', 'xy')")

[('xcxc', 'xcxc', 'xcxc')]
[('ba', 'xxx')]

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.
• Regular expressions: pattern matching and regexp_replace.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.