--- title: "upper" slug: "dev-database-reference-sql-functions-string-upper" updated: 2026-10-30T18:18:56Z published: 2026-10-30T18:18:56Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-upper" --- > ## 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. # upper *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.* Returns a string with every lower-case letter converted to upper case, including accented and other non-ASCII letters. `ucase` is the same function. ## Syntax ``` upper(s) ucase(s) ``` | Argument | Type | Description | | --- | --- | --- | | `s` | `VARCHAR` | The string to convert. | **Returns:** `VARCHAR`. **Aliases:** `ucase`. ## 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_upper`. 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 four orders. The SKUs and country codes were typed in mixed case. 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_upper"' 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}.orders" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, sku VARCHAR, country VARCHAR, city VARCHAR)") run(f"INSERT INTO {TABLE} VALUES " "(1, 'ab-100', 'gb', 'London'), " "(2, 'Cd-200', 'Fr', 'Paris'), " "(3, 'ef-300', 'FR', 'Straßburg'), " "(4, NULL, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `upper` maps each lower-case letter to its upper-case form, one character at a time, and leaves everything else (capitals, digits, punctuation, spaces, emoji, combining marks) unchanged. It covers Unicode letters, not only ASCII. In every case below, one character maps to one character, so the result has as many characters as the input: | Input | Result | Note | | --- | --- | --- | | `'é'` (U+00E9) | `'É'` | Accented small letters become accented capitals. | | `'straße'` | `'STRAẞE'` | ß becomes the capital sharp s ẞ (U+1E9E), not `SS`. | | `'ı'` (dotless i, U+0131) | `'I'` | | | `'dž'` (U+01C6) | `'DŽ'` (U+01C4) | The upper-case digraph, not the title-case form Dž. | | `'Dž'` (the title-case digraph, U+01C5) | `'DŽ'` (U+01C4) | | | `'fi'` (the fi ligature, U+FB01) | `'fi'`, unchanged | The ligature isn't expanded to `FI`. | | `'e\u0301'` (e and a combining accent) | `'E\u0301'` | The letter changes; the combining accent stays a separate code point. | `upper` takes no language argument, so the mapping is the same for all text. ## NULLs and edge cases | Input | Result | | --- | --- | | `NULL` | `NULL` | | `''` (empty string) | `''` | | `'straße'` | `'STRAẞE'`, still 6 characters | | `'fi'` | `'fi'` | | `'ı'` | `'I'` | | `'dž'` | `'DŽ'` | | `'é'` (U+00E9) | `'É'` | | `'e\u0301'` (`e` followed by U+0301) | `'E\u0301'`: `E` followed by U+0301, displayed as É | | `ucase('a')` | `'A'` | | A `BLOB` | Error: `No function matches the given name and argument types 'upper(BLOB)'` | | A list, such as `['a', 'b']` | Error: `No function matches the given name and argument types 'upper(VARCHAR[])'` | | A number, date or boolean | Error: `No function matches the given name and argument types`, for example `'upper(INTEGER_LITERAL)'` | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-string-upper#set-up-the-examples): ``` run("SELECT upper(NULL), upper(''), upper('straße'), length(upper('straße')), upper('fi'), upper('ı'), upper('dž')") run("SELECT upper('é'), upper('e\u0301'), ucase('a')") run("SELECT upper('\\xAA'::BLOB)") run("SELECT upper(['a', 'b'])") run("SELECT upper(123)") ``` ``` [(None, '', 'STRAẞE', 6, 'fi', 'I', 'DŽ')] [('É', 'É', 'A')] InternalError: Binder Error: No function matches the given name and argument types 'upper(BLOB)'. You might need to add explicit type casts. InternalError: Binder Error: No function matches the given name and argument types 'upper(VARCHAR[])'. You might need to add explicit type casts. InternalError: Binder Error: No function matches the given name and argument types 'upper(INTEGER_LITERAL)'. You might need to add explicit type casts. ``` ## Examples The SKUs, country codes and cities in upper case, with `ucase` on the city. `ß` in row 3 becomes `ẞ`: ``` run(f"SELECT id, upper(sku), upper(country), ucase(city) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 'AB-100', 'GB', 'LONDON'), (2, 'CD-200', 'FR', 'PARIS'), (3, 'EF-300', 'FR', 'STRAẞBURG'), (4, None, None, None)] ``` Select the orders for France, however the country code was typed: ``` run(f"SELECT id, sku FROM {TABLE} WHERE upper(country) = 'FR' ORDER BY id LIMIT 10") ``` ``` [(2, 'Cd-200'), (3, 'ef-300')] ``` ## 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 `upper()` of a column is always computed by the Query Engine: - **In the `SELECT` list,** the scan reads the column and the Query Engine computes `upper()` for each row it returns. - **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step in the Query Engine computes `upper()` and keeps the rows that match. No rows are skipped in storage. The plan of the France example shows the `FILTER` step above the scan: ``` plan(f"SELECT id FROM {TABLE} WHERE upper(country) = 'FR'") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (upper(country) = 'FR') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ country │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Normalize the case when you write the rows.** If you always compare codes in upper case, store them in upper case, and apply `upper()` to the value you search for instead of the column. `country = upper('fr')` compares the raw column with a constant, which is pushed down to VAST DataBase, so rows that don't match never leave storage. In this table only row 3 was stored as `FR`, so it's the only match; the plan has no `FILTER` step: ``` run(f"SELECT id, sku FROM {TABLE} WHERE country = upper('fr') ORDER BY id LIMIT 10") plan(f"SELECT id FROM {TABLE} WHERE country = upper('fr')") ``` ``` [(3, 'ef-300')] ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` - **Combine it with a pushed-down condition.** In `id > 1 AND upper(country) = 'FR'`, `id > 1` is still evaluated in VAST DataBase, so `upper()` runs only on the rows that pass it. - **`COLLATE nocase` doesn't avoid the function.** It's planned as a `lower()` filter; see [COLLATE nocase and noaccent](/develop/docs/dev-database-reference-sql-functions-string-collate). ## 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 `upper(VARCHAR)` as the only form. - **One character for one character.** `ß` becomes `ẞ`, and ligatures such as `fi` stay as they are. If a downstream system expects `SS` or `FI`, replace those characters in your code or with [replace](/develop/docs/dev-database-reference-sql-functions-string-replace). - **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 | Function | What it does | Result for `'Straße'` (`upper` gives `'STRAẞE'`) | | --- | --- | --- | | `ucase` | the same function as `upper` | `'STRAẞE'` | | [lower](/develop/docs/dev-database-reference-sql-functions-string-lower) (also `lcase`) | converts to lower case | `'straße'` | | [COLLATE nocase](/develop/docs/dev-database-reference-sql-functions-string-collate) | compares two strings as `lower()` of each | `'Straße' COLLATE nocase = 'STRAẞE'` is `true` | | [ILIKE](/develop/docs/dev-database-reference-sql-functions-operators-and-predicates#like-and-ilike) | pattern match without regard to case | see its page | ``` run("SELECT upper('Straße'), ucase('Straße'), lower('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](/develop/docs/dev-database-reference-sql-functions-string-text-basics): all the text functions. - [lower](/develop/docs/dev-database-reference-sql-functions-string-lower) and [COLLATE nocase and noaccent](/develop/docs/dev-database-reference-sql-functions-string-collate). - [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.