url_encode

Applies to VAST Cluster 5.5.

Functions › Text basics › Unicode and URLs. Percent-encodes a string so it can be placed in a URL: spaces, reserved characters such as &, =, / and ?, and characters outside ASCII become % followed by two hexadecimal digits for each UTF-8 byte. It has no aliases; url_decode reverses it.

Syntax

url_encode(s)

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_url_encode. 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 file names. The last row has no name:

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

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, name VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'report-2026.pdf'), "
    "(2, 'Q3 results.xlsx'), "
    "(3, 'R&D plan.txt'), "
    "(4, 'caf\u00e9 menu.pdf'), "
    "(5, NULL)")

[]
[]
[(5,)]

What it does

url_encode turns the string into UTF-8 bytes and writes each byte either as itself or as % followed by its value in two upper-case hexadecimal digits:

• Kept as they are: ASCII letters, digits and the four characters -, ., _ and ~.
• Encoded: everything else, including the space, the characters that have a meaning in a URL (/, ?, #, &, =, +, %) and every byte of a character outside ASCII.

Because / is encoded too, encode each part of a URL separately (a path segment, a query parameter's value) and join the parts with the separators, rather than encoding a whole URL.

The encoding depends on the bytes, so the same-looking text in two Unicode forms gives two encodings: the decomposed 'e\u0301' gives e%CC%81, the precomposed é gives %C3%A9. Apply nfc_normalize first if you need one encoding for both.

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 url_encode(NULL), url_encode(''), url_encode('~-._'), url_encode('a+b'), url_encode('?#%'), "
    "url_encode('AZaz0189')")
run("SELECT url_encode('e\u0301'), url_encode('\u00e9'), url_encode('e\u0301 🇫🇷 ẞ')")
run("SELECT url_encode(123)")

[(None, '', '~-._', 'a%2Bb', '%3F%23%25', 'AZaz0189')]
[('e%CC%81', '%C3%A9', 'e%CC%81%20%F0%9F%87%AB%F0%9F%87%B7%20%E1%BA%9E')]
InternalError: Binder Error: No function matches the given name and argument types 'url_encode(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

A download link for each file. The name is encoded as one path segment and joined to the fixed part of the URL with ||; the NULL name gives a NULL link:

run(f"SELECT id, 'https://files.example.com/shared/' || url_encode(name) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'https://files.example.com/shared/report-2026.pdf'), (2, 'https://files.example.com/shared/Q3%20results.xlsx'), (3, 'https://files.example.com/shared/R%26D%20plan.txt'), (4, 'https://files.example.com/shared/caf%C3%A9%20menu.pdf'), (5, None)]

Find the names that change when they're encoded, that is, the ones that contain characters that aren't safe in a URL:

run(f"SELECT id, name FROM {TABLE} WHERE url_encode(name) <> name ORDER BY id LIMIT 10")

[(2, 'Q3 results.xlsx'), (3, 'R&D plan.txt'), (4, 'café menu.pdf')]

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

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

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (url_encode(name) != name)│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            name           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Filter on the raw column, encode in the SELECT list. Encoding is for output: compare the stored name, not its encoding, in WHERE. A comparison of the raw column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage, and url_encode() runs only on the rows returned. The plan for name = 'R&D plan.txt' has no FILTER step:

plan(f"SELECT 'https://files.example.com/shared/' || url_encode(name) FROM {TABLE} WHERE name = 'R&D plan.txt'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│  ('https://files.example  │
│      .com/shared/' ||     │
│      url_encode(name))    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│     Projections: name     │
│                           │
└───────────────────────────┘

• Combine it with a pushed-down condition. In id > 100 AND url_encode(name) <> name, id > 100 is still evaluated in VAST DataBase, so url_encode() runs only on the rows that pass it.

Limitations and errors

• Strings only. A number, decimal, date, boolean, BLOB or list fails with No function matches the given name and argument types. Cast a number first: url_encode(CAST(n AS VARCHAR)).
• One encoding, no options. Spaces are always %20, never +, and / is always encoded. There's no argument to keep characters unencoded.
• Unicode form matters. See What it does.

run("SELECT url_encode(123)")
run("SELECT url_encode('\\xAA'::BLOB)")
run("SELECT url_encode(CAST(123 AS VARCHAR))")

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

Similar functions

run("SELECT url_encode('a b'), url_decode('a%20b'), url_decode(url_encode('a b/\u00e9'))")
run("SELECT to_base64('a'::BLOB)")

[('a%20b', 'a b', 'a b/é')]
[('YQ==',)]

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.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.
• url_decode and nfc_normalize.