trim

Applies to VAST Cluster 5.5.

Functions › 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)

Returns: VARCHAR.

Related: trim(LEADING ...) is ltrim and trim(TRAILING ...) is rtrim.

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_string_trim. 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 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, "<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:

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

The same cases, as the cluster returns them. They use the table and helpers from 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 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, 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

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: all the trimming and padding functions.
• ltrim, rtrim, lpad and rpad.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.