--- title: "trim" slug: "dev-database-reference-sql-functions-string-trim" updated: 2026-10-30T18:18:56Z published: 2026-10-30T18:18:56Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-trim" --- > ## 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. # trim *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Trimming and padding](/develop/docs/dev-database-reference-sql-functions-string-trimming-and-padding) › Trimming and padding.* Removes characters from both ends of a string: spaces by default, or any of the characters you list. The standard SQL form `trim(BOTH | LEADING | TRAILING characters FROM s)` trims both ends, the start or the end. ## Syntax ``` trim(s) trim(s, characters) trim(BOTH characters FROM s) trim(LEADING characters FROM s) trim(TRAILING characters FROM s) trim(characters FROM s) trim(BOTH FROM s) trim(LEADING FROM s) trim(TRAILING FROM s) trim(FROM s) ``` | Argument | Type | Description | | --- | --- | --- | | `s` | `VARCHAR` | The string to trim. | | `characters` | `VARCHAR` | Optional. The set of characters to remove, in any order. Leave it out to remove spaces. | | `BOTH`, `LEADING`, `TRAILING` | keyword | Which end to trim: both (the default), the start or the end. | **Returns:** `VARCHAR`. **Related:** `trim(LEADING ...)` is [ltrim](/develop/docs/dev-database-reference-sql-functions-string-ltrim) and `trim(TRAILING ...)` is [rtrim](/develop/docs/dev-database-reference-sql-functions-string-rtrim). ## 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_string_trim`. 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 product codes as they arrived from different systems: with spaces around them, with a no-break space (U+00A0) in front, with asterisks around them, and with a tab before and a newline after. 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_string_trim"' 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}.codes" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, code VARCHAR)") run(f"INSERT INTO {TABLE} VALUES " "(1, ' SKU-1 '), " "(2, '\u00a0SKU-2'), " "(3, '**SKU-3**'), " "(4, '\tSKU-4\n'), " "(5, NULL)") ``` ``` [] [] [(5,)] ``` ## What it does **Without `characters`,** `trim(s)` removes spaces from both ends. That includes Unicode spaces such as the no-break space (U+00A0), the ideographic space (U+3000) and the em space (U+2003), but **not tabs or newlines**: `trim('\t a \n')` returns the string unchanged, because it starts with a tab and ends with a newline. Spaces inside the string stay: `trim(' a b ')` returns `'a b'`. **With `characters`,** `trim(s, characters)` removes every character in the set from both ends, until it reaches one that isn't in the set. The set has no order and no wildcards: | Call | Result | | --- | --- | | `trim('xxaxx', 'x')` | `'a'` | | `trim('xyaxy', 'yx')`, `trim('xyaxy', 'xy')` | `'a'`: the order of the set doesn't matter | | `trim('abcba', 'ab')` | `'c'` | | `trim('0042.500', '0')` | `'42.5'` | | `trim('%a_', '%_')`, `trim('[a]', '[]')` | `'a'`: `%`, `_` and brackets are ordinary characters | | `trim('xax', 'X')` | `'xax'`: the match is case-sensitive | | `trim(' \ta\t ', ' \t')` | `'a'`: list the tab to remove it | | `trim('aaa', 'a')` | `''` | | `trim('a', '')` | `'a'`: an empty set removes nothing | **The SQL standard form** puts the end first: `trim(BOTH 'x' FROM 'xax')` returns `'a'`, `trim(LEADING 'x' FROM 'xax')` returns `'ax'` and `trim(TRAILING 'x' FROM 'xax')` returns `'xa'`. Without the characters, as in `trim(LEADING FROM ' a ')`, it removes spaces. `trim('x' FROM 'xax')` and `trim(FROM s)` trim both ends. **The set is made of code points.** An accented letter typed as `e` and a combining accent is two code points: `trim('e\u0301ae\u0301', '\u0301')` removes only the accent at the end and returns `'e\u0301ae'`, while the set `'e\u0301'` removes both. A flag emoji is two code points too, so a set with only its first half removes only that half. ## NULLs and edge cases | Input | Result | | --- | --- | | `s` is NULL | `NULL` | | `characters` is NULL, including `trim(BOTH NULL FROM s)` | `NULL` | | `''` (empty string) | `''` | | `trim(' ')` (spaces only) | `''` | | `trim('\u00a0a\u00a0')`, `trim('\u3000a\u3000')` (Unicode spaces) | `'a'` | | `trim('\t a \n')`, `trim('\ta\t')`, `trim('\na\n')` | unchanged | | `trim('e\u0301ae\u0301', '\u0301')` | `'e\u0301ae'` | | `trim(LEADING '' FROM 'a')` | `'a'` | | `s` a number, date, boolean, `BLOB` or list, or a number as `characters` | Error: `No function matches the given name and argument types` | | Three arguments, such as `trim(s, ' ', ' ')` | Error: `No function matches the given name and argument types` | 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-trim#set-up-the-examples): ``` run("SELECT trim(NULL), trim(' a ', NULL), trim(NULL, 'x'), trim(BOTH NULL FROM 'a'), trim(''), trim(' ')") run("SELECT trim('\u00a0a\u00a0'), trim('\u3000a\u3000'), trim('\u2003a'), trim(' a b ')") run("SELECT trim('\t a \n'), trim('\ta\t'), trim('\na\n')") run("SELECT trim('xxaxx', 'x'), trim('xyaxy', 'yx'), trim('abcba', 'ab'), trim('0042.500', '0'), " "trim('%a_', '%_'), trim('xax', 'X'), trim(' \ta\t ', ' \t'), trim('aaa', 'a'), trim('a', '')") run("SELECT trim(BOTH 'x' FROM 'xax'), trim(LEADING 'x' FROM 'xax'), trim(TRAILING 'x' FROM 'xax'), " "trim(LEADING FROM ' a '), trim(TRAILING FROM ' a '), trim('x' FROM 'xax'), trim(FROM ' a ')") run("SELECT trim('e\u0301ae\u0301', '\u0301'), trim('e\u0301ae\u0301', 'e\u0301'), " "trim('\U0001F1EB\U0001F1F7a\U0001F1EB\U0001F1F7', '\U0001F1EB')") run("SELECT trim(123)") run("SELECT trim('xax', 'x', 'y')") ``` ``` [(None, None, None, None, '', '')] [('a', 'a', 'a', 'a b')] [('\t a \n', '\ta\t', '\na\n')] [('a', 'a', 'c', '42.5', 'a', 'xax', 'a', '', 'a')] [('a', 'ax', 'xa', 'a ', ' a', 'a', 'a')] [('éae', 'a', '🇷a🇫🇷')] InternalError: Binder Error: No function matches the given name and argument types 'trim(INTEGER_LITERAL)'. You might need to add explicit type casts. InternalError: Binder Error: No function matches the given name and argument types 'trim(STRING_LITERAL, STRING_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts. ``` ## Examples Clean the codes. `trim(code)` removes the spaces of rows 1 and 2, but not the tab and newline of row 4; listing the characters removes the asterisks of row 3. The brackets show where each result starts and ends: ``` run(f"SELECT id, '[' || trim(code) || ']', trim(code, '*'), trim(BOTH ' *' FROM code) " f"FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, '[SKU-1]', ' SKU-1 ', 'SKU-1'), (2, '[SKU-2]', '\xa0SKU-2', '\xa0SKU-2'), (3, '[**SKU-3**]', 'SKU-3', 'SKU-3'), (4, '[\tSKU-4\n]', '\tSKU-4\n', '\tSKU-4\n'), (5, None, None, None)] ``` To remove the tab and newline too, list them with the space and the no-break space, or use [regexp_replace](/develop/docs/dev-database-reference-sql-functions-string-regexp-replace) with `\s`, which removes the tab and newline but leaves the no-break space of row 2. In Python, `'\\s'` is the SQL text `\s`, and `'\t'` and `'\n'` are a real tab and newline: ``` run(f"SELECT id, trim(code, ' \t\n\u00a0*'), regexp_replace(code, '^\\s+|\\s+$', '', 'g') " f"FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 'SKU-1', 'SKU-1'), (2, 'SKU-2', '\xa0SKU-2'), (3, 'SKU-3', '**SKU-3**'), (4, 'SKU-4', 'SKU-4'), (5, None, None)] ``` Find a code whatever the spaces around it: ``` run(f"SELECT id FROM {TABLE} WHERE trim(code) = 'SKU-2' ORDER BY id LIMIT 10") ``` ``` [(2,)] ``` ## 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 `trim()` is always computed by the Query Engine: - **In the `SELECT` list,** the scan reads the column and the Query Engine trims it for each row it returns. - **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step in the Query Engine trims it 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 trim(code) = 'SKU-2'") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ ("trim"(code) = 'SKU-2') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ code │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Trim when you write, not when you read.** Store trimmed values, so that a query can compare the column with a constant, `code = 'SKU-2'`, and the comparison is pushed down to VAST DataBase. The plan of a raw column compared with a constant has no `FILTER` step. - **Combine it with a pushed-down condition.** In `id > 1 AND trim(code) = 'SKU-2'`, `id > 1` is still evaluated in VAST DataBase, so `trim()` runs only on the rows that pass it. ``` plan(f"SELECT id FROM {TABLE} WHERE code = 'SKU-2'") ``` ``` ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` ## Limitations and errors - **Tabs and newlines stay unless you list them.** `trim(s)` removes spaces only. List the characters, as in `trim(s, ' \t\n')`, or use `regexp_replace(s, '^\s+|\s+$', '', 'g')`. - **A set of characters, not a string.** `trim(s, 'xy')` removes any mix of `x` and `y`, not the text `xy`. To remove a word at the ends, use [regexp_replace](/develop/docs/dev-database-reference-sql-functions-string-regexp-replace), such as `regexp_replace(s, '^(xy)+|(xy)+$', '', 'g')`. - **`VARCHAR` only.** A number fails with `No function matches the given name and argument types 'trim(INTEGER_LITERAL)'`. Cast it first: `trim(CAST(n AS VARCHAR), '0')`. - **No `btrim`.** It fails with `Scalar Function with name btrim does not exist!`. Use `trim(s, characters)`. ``` run("SELECT trim('xyaxy', 'xy'), trim('xyzax', 'xy'), regexp_replace('xyaxy', '^(xy)+|(xy)+$', '', 'g')") run("SELECT trim(CAST(1200 AS VARCHAR), '0')") run("SELECT btrim('xax', 'x')") ``` ``` [('a', 'za', 'a')] [('12',)] InternalError: Catalog Error: Scalar Function with name btrim does not exist! ``` ## Similar functions | Function | Removes | Result for `'xyaxy'` and `'xy'` | | --- | --- | --- | | `trim(s, 'xy')` | the characters at both ends | `'a'` | | [ltrim](/develop/docs/dev-database-reference-sql-functions-string-ltrim) `(s, 'xy')` | the characters at the start | `'axy'` | | [rtrim](/develop/docs/dev-database-reference-sql-functions-string-rtrim) `(s, 'xy')` | the characters at the end | `'xya'` | | [replace](/develop/docs/dev-database-reference-sql-functions-string-replace) `(s, 'xy', '')` | the text everywhere | `'a'` | | [translate](/develop/docs/dev-database-reference-sql-functions-string-translate) `(s, 'xy', '')` | the characters everywhere | `'a'` | | [regexp_replace](/develop/docs/dev-database-reference-sql-functions-string-regexp-replace) | what a pattern matches | | ``` run("SELECT trim('xyaxy', 'xy'), ltrim('xyaxy', 'xy'), rtrim('xyaxy', 'xy'), replace('xyaxy', 'xy', ''), " "translate('xyaxy', 'xy', '')") ``` ``` [('a', 'axy', 'xya', 'a', 'a')] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Trimming and padding](/develop/docs/dev-database-reference-sql-functions-string-trimming-and-padding): all the trimming and padding functions. - [ltrim](/develop/docs/dev-database-reference-sql-functions-string-ltrim), [rtrim](/develop/docs/dev-database-reference-sql-functions-string-rtrim), [lpad](/develop/docs/dev-database-reference-sql-functions-string-lpad) and [rpad](/develop/docs/dev-database-reference-sql-functions-string-rpad). - [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.