strip_accents

Applies to VAST Cluster 5.5.

Functions › Text basics › Unicode and URLs. Removes accents and other combining marks from the letters of a string, so Crème Brûlée becomes Creme Brulee. It has no aliases.

Syntax

strip_accents(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_strip_accents. 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 cities. Málaga is written decomposed, with a combining accent; the last row has no city:

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

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}.cities"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, city VARCHAR, country VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Z\u00fcrich', 'CH'), "
    "(2, 'Krak\u00f3w', 'PL'), "
    "(3, '\u0141\u00f3d\u017a', 'PL'), "
    "(4, 'Ma\u0301laga', 'ES'), "
    "(5, 'Zurich', 'CH'), "
    "(6, NULL, 'FR')")

[]
[]
[(6,)]

What it does

strip_accents returns the string with the accents taken off its letters. Everything else is kept: case, spaces, and characters that have no accent, such as emoji and ẞ.

It removes the marks that Unicode can write as a separate combining accent on a base letter: acute, grave, circumflex, diaeresis, ring, and so on. It works on both forms of an accented letter, precomposed (é, one code point) and decomposed (e followed by a combining accent, written 'e\u0301' on this page):

Letters that Unicode treats as letters in their own right, not as a base letter with an accent, are kept:

If you need those letters replaced too, follow strip_accents with replace or translate, for example translate(strip_accents(s), 'Łłø', 'Llo').

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 strip_accents(NULL), strip_accents(''), strip_accents('e\u0301'), strip_accents('e\u0301 🇫🇷 ẞ'), "
    "strip_accents('\u00df'), strip_accents('\u00f8')")
run("SELECT strip_accents('Cr\u00e8me Br\u00fbl\u00e9e'), strip_accents('\u00c5ngstr\u00f6m'), "
    "strip_accents('\u0141\u00f3d\u017a')")
run("SELECT strip_accents(123)")

[(None, '', 'e', 'e 🇫🇷 ẞ', 'ß', 'ø')]
[('Creme Brulee', 'Angstrom', 'Łodz')]
InternalError: Binder Error: No function matches the given name and argument types 'strip_accents(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Each city with its accents removed. The precomposed letters of rows 1 to 3 and the combining accent of row 4 are all removed; the Ł of row 3 is kept:

run(f"SELECT id, city, strip_accents(city) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'Zürich', 'Zurich'), (2, 'Kraków', 'Krakow'), (3, 'Łódź', 'Łodz'), (4, 'Málaga', 'Malaga'), (5, 'Zurich', 'Zurich'), (6, None, None)]

Search without regard to accents: Zurich finds both spellings of the city:

run(f"SELECT id, city FROM {TABLE} WHERE strip_accents(city) = 'Zurich' ORDER BY id LIMIT 10")

[(1, 'Zürich'), (5, 'Zurich')]

Search without regard to accents or case, by combining it with lower:

run(f"SELECT id, city FROM {TABLE} WHERE lower(strip_accents(city)) = 'krakow' ORDER BY id LIMIT 10")

[(2, 'Kraków')]

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

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

COLLATE noaccent uses the same function: city COLLATE noaccent = 'Zurich' becomes a strip_accents(city) = 'Zurich' filter, so it isn't pushed down either. See COLLATE nocase and noaccent.

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

plan(f"SELECT id FROM {TABLE} WHERE strip_accents(city) = 'Zurich'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (strip_accents(city) =   │
│         'Zurich')         │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            city           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store a search key. If you search without regard to accents often, store the accent-free form (for example lower(strip_accents(city)), computed by your application when it writes the rows) in its own column. A comparison of that raw column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan for city = 'Zurich' has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE city = 'Zurich'")

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

• Combine it with a pushed-down condition. In country = 'PL' AND strip_accents(city) = 'Krakow', country = 'PL' is still evaluated in VAST DataBase, so strip_accents() 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.
• Accents only. Letters such as Ł, ø, đ, æ and ß are kept. See What it does; the last line of this block replaces them with translate.
• Case is kept. strip_accents('Crème') is 'Creme', so it doesn't equal 'creme'. Combine it with lower to ignore case as well.

run("SELECT strip_accents(123)")
run("SELECT strip_accents('\\xAA'::BLOB)")
run("SELECT strip_accents('Cr\u00e8me') = 'creme', lower(strip_accents('Cr\u00e8me')) = 'creme'")
run("SELECT translate(strip_accents('\u0141\u00f3d\u017a'), '\u0141\u0142\u00f8', 'Llo')")

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

Similar functions

run("SELECT strip_accents('Cr\u00e8me'), nfc_normalize('Cre\u0300me') = 'Cr\u00e8me', "
    "'Cr\u00e8me' COLLATE noaccent = 'creme'")
run("SELECT '\u00c9' COLLATE nocase.noaccent = 'e'")

[('Creme', True, False)]
[(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.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.
• nfc_normalize, COLLATE nocase and noaccent and lower.