--- title: "COLLATE nocase and noaccent" slug: "dev-database-reference-sql-functions-string-collate" updated: 2026-10-30T18:18:56Z published: 2026-10-30T18:18:56Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-collate" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # COLLATE nocase and noaccent *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Text basics](/develop/docs/dev-database-reference-sql-functions-string-text-basics) › Case.* `COLLATE` after a string in a comparison compares it without regard to case (`nocase`), to accents (`noaccent`), or to both (`nocase.noaccent`). Language collations, such as `COLLATE de`, aren't available. ## Syntax ``` s COLLATE nocase = t s COLLATE noaccent = t s COLLATE nocase.noaccent = t ``` | Argument | Type | Description | | --- | --- | --- | | `s` | `VARCHAR` | The string to compare: a column or a constant. `COLLATE` follows it directly. | | `t` | `VARCHAR` | The string to compare it with. | | collation | `nocase`, `noaccent` or `nocase.noaccent` | What to ignore: case, accents, or both. | **Returns:** `BOOLEAN`, the result of the comparison. **Aliases:** none. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_collate`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), 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 customers. Rows 2 and 3 are the same person, typed with and without accents and capitals. `name_key` is a lower-case, accent-free copy of the name, 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_collate"' 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, "") 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}.customers" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR, city VARCHAR, name_key VARCHAR)") run(f"INSERT INTO {TABLE} VALUES " "(1, 'Ada Lovelace', 'London', 'ada lovelace'), " "(2, 'José Martí', 'Havana', 'jose marti'), " "(3, 'jose marti', 'HAVANA', 'jose marti'), " "(4, 'Renée Dupont', 'Montréal', 'renee dupont'), " "(5, NULL, NULL, NULL)") ``` ``` [] [] [(5,)] ``` ## What it does A plain `=` on strings compares them exactly: `'abc' = 'ABC'` is `false`. `COLLATE` changes what counts as equal: | Collation | Ignores | In a plan, the column becomes | Example | Result | | --- | --- | --- | --- | --- | | none | nothing | `s` | `'abc' = 'ABC'` | `false` | | `nocase` | case | `lower(s)`, and the constant is lower-cased too | `'abc' COLLATE nocase = 'ABC'` | `true` | | `noaccent` | accents, but not case | `strip_accents(s)` | `'Crème' COLLATE noaccent = 'creme'` | `false` (C and c differ) | | `nocase.noaccent` | case and accents | `lower(strip_accents(s))` | `'É' COLLATE nocase.noaccent = 'e'` | `true` | The Query Engine rewrites the comparison into the function it stands for. In a plan, `city COLLATE nocase = 'havana'` shows up as `lower(city) = 'havana'`, `city COLLATE nocase = 'HAVANA'` also as `lower(city) = 'havana'`, `name COLLATE noaccent = 'cafe'` as `strip_accents(name) = 'cafe'`, and `name COLLATE nocase.noaccent = 'cafe'` as `lower(strip_accents(name)) = 'cafe'`. So `nocase` follows the rules of [lower](/develop/docs/dev-database-reference-sql-functions-string-lower), and `noaccent` those of [strip_accents](/develop/docs/dev-database-reference-sql-functions-string-strip-accents). `noaccent` also removes an accent written as a combining mark: `'e\u0301' COLLATE noaccent = 'e'` (`e` followed by U+0301) is `true`. `noaccent` on its own is still case-sensitive. To ignore both, use `nocase.noaccent`. Where else `COLLATE` works: | Use | Example | Result | | --- | --- | --- | | Upper-case collation name | `'a' COLLATE NOCASE = 'A'` | `true` | | On the right operand | `'A' = 'a' COLLATE nocase` | `true` | | Ordering comparisons | `'b' COLLATE nocase > 'A'` | `true` | | `IN` | `'ABC' COLLATE nocase IN ('abc')` | `true` | | `ORDER BY` | `ORDER BY s COLLATE nocase` | rows sorted as `lower(s)` would sort them, NULLs last | ## NULLs and edge cases | Input | Result | | --- | --- | | `'abc' = 'ABC'` | `false` | | `'abc' COLLATE nocase = 'ABC'` | `true` | | `'Crème' COLLATE noaccent = 'creme'` | `false` | | `'É' COLLATE nocase.noaccent = 'e'` | `true` | | A `NULL` value, such as row 5 in [Examples](/develop/docs/dev-database-reference-sql-functions-string-collate#examples) | `NULL` (`None` in Python) | | `COLLATE de` or another language collation | Error: `Collation with name "de" is not in the catalog` | The same cases, as the cluster returns them; the last example in [Examples](/develop/docs/dev-database-reference-sql-functions-string-collate#examples) shows the `NULL` row. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-string-collate#set-up-the-examples): ``` run("SELECT 'abc' = 'ABC', 'abc' COLLATE nocase = 'ABC', 'Crème' COLLATE noaccent = 'creme', 'É' COLLATE nocase.noaccent = 'e'") run("SELECT 'a' COLLATE de = 'A'") ``` ``` [(False, True, False, True)] InternalError: Catalog Error: Collation with name "de" is not in the catalog, but it exists in the icu extension. ``` ## Examples Find the customers in Havana, however the city was typed: ``` run(f"SELECT id, name, city FROM {TABLE} WHERE city COLLATE nocase = 'havana' ORDER BY id LIMIT 10") ``` ``` [(2, 'José Martí', 'Havana'), (3, 'jose marti', 'HAVANA')] ``` Ignore accents only. Row 2 matches; row 3 doesn't, because `noaccent` still compares case: ``` run(f"SELECT id, name FROM {TABLE} WHERE name COLLATE noaccent = 'Jose Marti' ORDER BY id LIMIT 10") ``` ``` [(2, 'José Martí')] ``` Ignore both, and show the comparison for every row. Rows 2 and 3 match; the row with no name gives `None`: ``` run(f"SELECT id, name COLLATE nocase.noaccent = 'jose marti' FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, False), (2, True), (3, True), (4, False), (5, None)] ``` ## 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 `COLLATE` turns the comparison into a function of the column, so it's always evaluated by the Query Engine: - **In the `SELECT` list,** the scan reads the column and the Query Engine computes the comparison 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()` or `strip_accents()` and keeps the rows that match. No rows are skipped in storage. The plans of the first two examples show the rewritten comparison in a `FILTER` step above the scan: ``` plan(f"SELECT id FROM {TABLE} WHERE city COLLATE nocase = 'havana'") plan(f"SELECT id FROM {TABLE} WHERE name COLLATE noaccent = 'Jose Marti'") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (lower(city) = 'havana') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ city │ │ id │ │ │ └───────────────────────────┘ ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (strip_accents(name) = │ │ 'Jose Marti') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ name │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Store a normalized key and compare it exactly.** If you search a column without regard to case or accents often, write a lower-case, accent-free copy when you store the rows (here `name_key`) and compare it with a normalized constant. 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 has no `FILTER` step: ``` run(f"SELECT id, name FROM {TABLE} WHERE name_key = 'jose marti' ORDER BY id LIMIT 10") plan(f"SELECT id FROM {TABLE} WHERE name_key = 'jose marti'") ``` ``` [(2, 'José Martí'), (3, 'jose marti')] ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` - **Combine it with a pushed-down condition.** In `id > 1 AND city COLLATE nocase = 'havana'`, `id > 1` is still evaluated in VAST DataBase, so the `lower()` filter runs only on the rows that pass it. ## Limitations and errors - **Only `nocase`, `noaccent` and `nocase.noaccent`.** Language collations, such as `COLLATE de` for German sorting rules, aren't available in the VAST Query Engine. They fail with `Catalog Error: Collation with name "de" is not in the catalog, but it exists in the icu extension.` Use `nocase` or `noaccent`, or apply language rules in your code. - **`noaccent` alone keeps case.** `'Crème' COLLATE noaccent = 'creme'` is `false`; use `nocase.noaccent`. - **Strings only.** `COLLATE` on the bare literal `NULL` fails with `Binder Error: collations are only supported for type varchar`. A NULL from a `VARCHAR` column is fine and gives `NULL`. - **`GROUP BY s COLLATE nocase` limits the select list.** The select list can't use another expression of `s`: `SELECT lower(s), count(*) FROM t GROUP BY s COLLATE nocase` fails with `column "s" must appear in the GROUP BY clause or must be part of an aggregate function`. ``` run("SELECT 'a' COLLATE de = 'A'") run("SELECT 'Crème' COLLATE noaccent = 'creme', 'Crème' COLLATE nocase.noaccent = 'creme'") ``` ``` InternalError: Catalog Error: Collation with name "de" is not in the catalog, but it exists in the icu extension. [(False, True)] ``` ## Similar functions The same pair of strings, `'É'` and `'e'`, compared in different ways: | Comparison | Result | | --- | --- | | `'É' = 'e'` | `false` | | `'É' COLLATE nocase = 'e'` | `false`: [lower](/develop/docs/dev-database-reference-sql-functions-string-lower) of `'É'` is `'é'`, which still has its accent | | `'É' COLLATE noaccent = 'e'` | `false`: [strip_accents](/develop/docs/dev-database-reference-sql-functions-string-strip-accents) of `'É'` is `'E'`, which is still upper case | | `'É' COLLATE nocase.noaccent = 'e'` | `true` | | `lower(strip_accents('É')) = 'e'` | `true`: the same as `nocase.noaccent`, written with functions | For a pattern match without regard to case, see [ILIKE](/develop/docs/dev-database-reference-sql-functions-operators-and-predicates#like-and-ilike). ``` run("SELECT 'É' = 'e', 'É' COLLATE nocase = 'e', 'É' COLLATE noaccent = 'e', 'É' COLLATE nocase.noaccent = 'e'") run("SELECT lower('É'), strip_accents('É'), lower(strip_accents('É')) = 'e'") ``` ``` [(False, False, False, True)] [('é', 'E', True)] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Text basics](/develop/docs/dev-database-reference-sql-functions-string-text-basics): all the text functions. - [lower](/develop/docs/dev-database-reference-sql-functions-string-lower), [upper](/develop/docs/dev-database-reference-sql-functions-string-upper) and [strip_accents](/develop/docs/dev-database-reference-sql-functions-string-strip-accents): the functions the collations are planned as. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.