lower

Applies to VAST Cluster 5.5.

Functions › Text basics › Case. Returns a string with every upper-case letter converted to lower case, including accented, Greek and other non-ASCII letters. lcase is the same function.

Syntax

lower(s)
lcase(s)

Returns: VARCHAR.

Aliases: lcase.

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_lower. 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 accounts. email holds the address as the user typed it; email_lower holds the same address in lower case, written by the application when it stored the row. 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_lower"'

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}.accounts"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, email VARCHAR, email_lower VARCHAR, country VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Ada@Example.com', 'ada@example.com', 'GB'), "
    "(2, 'GRACE.HOPPER@EXAMPLE.COM', 'grace.hopper@example.com', 'US'), "
    "(3, 'JOSÉ@Example.COM', 'josé@example.com', 'CU'), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

lower maps each upper-case letter to its lower-case form, one character at a time, and leaves everything else (lower-case letters, digits, punctuation, spaces, emoji, combining marks) unchanged. It covers Unicode letters, not only ASCII:

lower takes no language argument, so the mapping is the same for all text: İ becomes a plain i. If you need language-specific rules, such as Turkish, where İ pairs with i and I with ı, apply them in your code.

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 lower(NULL), lower(''), lower('ẞ'), lower('İ'), lower('ΣΑΣ'), lower('ÀÉÎ'), lower('ABC123!'), lcase('A')")
run("SELECT lower('e\u0301 🇫🇷'), length(lower('e\u0301 🇫🇷'))")
run("SELECT lower('\\xAA'::BLOB)")
run("SELECT lower(['a', 'b'])")
run("SELECT lower(123)")

[(None, '', 'ß', 'i', 'σασ', 'àéî', 'abc123!', 'a')]
[('é 🇫🇷', 5)]
InternalError: Binder Error: No function matches the given name and argument types 'lower(BLOB)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'lower(VARCHAR[])'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'lower(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Each address in lower case, with lcase on the country code. É in row 3 becomes é:

run(f"SELECT id, lower(email), lcase(country) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'ada@example.com', 'gb'), (2, 'grace.hopper@example.com', 'us'), (3, 'josé@example.com', 'cu'), (4, None, None)]

Look an address up without regard to how it was typed:

run(f"SELECT id, email FROM {TABLE} WHERE lower(email) = 'grace.hopper@example.com' ORDER BY id LIMIT 10")

[(2, 'GRACE.HOPPER@EXAMPLE.COM')]

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 lower() of a column is always computed by the Query Engine:

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

lower() of a constant is different: it's computed before the scan, so email_lower = lower('Grace.Hopper@Example.com') is a comparison of the raw column with a constant, and is pushed down.

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

plan(f"SELECT id FROM {TABLE} WHERE lower(email) = 'grace.hopper@example.com'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   (lower(email) = 'grace  │
│   .hopper@example.com')   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           email           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store a lower-case copy and compare it with a lower-case constant. If you look up values without regard to case, write a lower-case column when you store the rows, and apply lower() to the value you search for instead of the column. The comparison is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan has no FILTER step:

run(f"SELECT id, email FROM {TABLE} WHERE email_lower = lower('Grace.Hopper@Example.com') ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE email_lower = lower('Grace.Hopper@Example.com')")

[(2, 'GRACE.HOPPER@EXAMPLE.COM')]
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• COLLATE nocase is lower() too. email COLLATE nocase = '...' is planned as a lower(email) filter, so it isn't faster; see COLLATE nocase and noaccent.
• Combine it with a pushed-down condition. In country = 'US' AND lower(email) = '...', country = 'US' is still evaluated in VAST DataBase, so lower() runs only on the rows that pass it.

Limitations and errors

• Strings only. A number, a date, a boolean, a list or a BLOB fails with No function matches the given name and argument types; the error lists lower(VARCHAR) as the only form.
• No language rules. lower takes no language argument; see What it does.
• No title case. initcap isn't available: it fails with Scalar Function with name initcap does not exist!. Capitalize words in your code.

run("SELECT initcap('ada lovelace')")

InternalError: Catalog Error: Scalar Function with name initcap does not exist!

Similar functions

run("SELECT lower('Straße'), lcase('Straße'), upper('Straße'), 'Straße' COLLATE nocase = 'STRAẞE'")

[('straße', 'straße', 'STRAẞE', True)]

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.
• upper and COLLATE nocase and noaccent.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.