--- title: "contains" slug: "dev-database-reference-sql-functions-string-contains" updated: 2026-10-30T18:18:56Z published: 2026-10-30T18:18:56Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-contains" --- > ## 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. # contains *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Search and split](/develop/docs/dev-database-reference-sql-functions-string-search-and-split) › Searching.* Returns true if a string contains a given substring anywhere. The Query Engine rewrites `LIKE '%ab%'` to it. With a list or a map as the first argument, the same name tests for an element or a key. ## Syntax ``` contains(s, substring) contains(list, element) contains(map, key) ``` | Argument | Type | Description | | --- | --- | --- | | `s` | `VARCHAR` | The string to search. | | `substring` | `VARCHAR` | The text to look for. | | `list`, `element` | a list and a value of its element type | Tests whether the list has the element ([list_contains](/develop/docs/dev-database-reference-sql-functions-lists-list-contains)). | | `map`, `key` | a map and a value of its key type | Tests whether the map has the key ([map_contains](/develop/docs/dev-database-reference-sql-functions-maps-map-contains)). | **Returns:** `BOOLEAN`. ## 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_contains`. 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 log lines. Rows 2 and 3 hold the same word, `café`, with the accent written two ways: as one character (U+00E9) and as `e` followed by a combining accent (U+0301). 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_contains"' 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}.events" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, msg VARCHAR, tags VARCHAR[])") run(f"INSERT INTO {TABLE} VALUES " "(1, 'ERROR disk full on /data', ['prod', 'storage']), " "(2, 'order: caf\u00e9 latte', ['prod']), " "(3, 'order: cafe\u0301 au lait, 50% off', []), " "(4, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `contains(s, substring)` is true when `substring` occurs anywhere in `s`. The comparison is code point by code point: - **Case matters.** `contains('ABC', 'b')` is false. To ignore case, test `lower(s)` ([lower](/develop/docs/dev-database-reference-sql-functions-string-lower)) or use [ILIKE](/develop/docs/dev-database-reference-sql-functions-string-ilike) `'%b%'`. - **The substring is plain text.** `%`, `_`, `.` and other characters that mean something in `LIKE` or a regular expression match only themselves, so `contains(s, '50%')` needs no escaping. - **Accents must be written the same way.** `contains('cafe\u0301', 'é')` (a decomposed `é` in the string, a precomposed one in the substring) is false, and `contains('e\u0301', '\u0301')` is true. Normalise the column with [nfc_normalize](/develop/docs/dev-database-reference-sql-functions-string-nfc-normalize) when its text may be written either way. - **An empty substring matches every string,** including the empty string. With a list, `contains(list, element)` is true when one of the list's elements equals `element`; it doesn't look inside the elements, so `contains(['a', 'b'], 'ab')` is false. ## NULLs and edge cases | Input | Result | | --- | --- | | `contains(NULL, 'll')`, `contains('abc', NULL)` | `NULL` | | `contains('abc', '')`, `contains('', '')` | `true` | | `contains('', 'a')` | `false` | | `contains('abc', 'abcd')` | `false` | | `contains('ABC', 'b')` | `false` | | `contains('aaa', 'aa')` | `true` | | `contains('a%b', '%')`, `contains('a_b', '_')` | `true` | | A line break, in a string that has one | `true` | | `contains('café', 'é')` (both precomposed) | `true` | | `contains('cafe\u0301', 'é')` (decomposed string, precomposed substring) | `false` | | `contains('e\u0301', '\u0301')` (the combining accent alone) | `true` | | `contains(['a', 'b'], 'a')`, `contains(['a', 'b'], 'ab')` | `true`, `false` | | A number, date, boolean, `BLOB` or struct as the first argument | Error: `No function matches the given name and argument types` | | A list of numbers with a string: `contains([1, 2], 'll')` | Error: `contains: Cannot match element of type 'VARCHAR' in a list of type 'INTEGER[]' - an explicit cast is required` | 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-contains#set-up-the-examples): ``` run("SELECT contains(NULL, 'll'), contains('abc', NULL), contains('abc', ''), contains('', ''), " "contains('', 'a'), contains('abc', 'abcd')") run("SELECT contains('ABC', 'b'), contains('aaa', 'aa'), contains('a%b', '%'), contains('a_b', '_'), " "contains('a\nb', '\n')") run("SELECT contains('caf\u00e9', '\u00e9'), contains('cafe\u0301', '\u00e9'), contains('e\u0301', '\u0301')") run("SELECT contains(['a', 'b'], 'a'), contains(['a', 'b'], 'ab')") run("SELECT contains(123, 'll')") run("SELECT contains([1, 2], 'll')") ``` ``` [(None, None, True, True, False, False)] [(False, True, True, True, True)] [(True, False, True)] [(True, False)] InternalError: Binder Error: No function matches the given name and argument types 'contains(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts. InternalError: Binder Error: contains: Cannot match element of type 'VARCHAR' in a list of type 'INTEGER[]' - an explicit cast is required ``` ## Examples Search each message for a word and for a literal `%`. The row with no message gives `NULL`: ``` run(f"SELECT id, contains(msg, 'disk'), contains(msg, '%') FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, True, False), (2, False, False), (3, False, True), (4, None, None)] ``` Keep the orders. Rows 2 and 3 both contain `café` on screen, but only row 2 matches a precomposed substring until the column is normalised: ``` run(f"SELECT id FROM {TABLE} WHERE contains(msg, 'caf\u00e9') ORDER BY id LIMIT 10") run(f"SELECT id FROM {TABLE} WHERE contains(nfc_normalize(msg), 'caf\u00e9') ORDER BY id LIMIT 10") ``` ``` [(2,)] [(2,), (3,)] ``` On the list column, `contains` tests for a whole element: ``` run(f"SELECT id, contains(tags, 'prod') FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, True), (2, True), (3, False), (4, 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 not a function of a column, so `contains()` is always computed by the Query Engine: - **In the `SELECT` list,** the scan reads the column and the Query Engine computes `contains()` for each row it returns. - **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step in the Query Engine keeps the rows that match. No rows are skipped in storage. `LIKE '%ab%'`, `regexp_matches(s, 'ab')` with a pattern that has no special characters, and `regexp_matches(s, regexp_escape('...'))` are all rewritten to `contains()`, so they run the same way. The plans show the `FILTER` step above the scan: ``` plan(f"SELECT id FROM {TABLE} WHERE contains(msg, 'disk')") plan(f"SELECT id FROM {TABLE} WHERE msg LIKE '%disk%'") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ contains(msg, 'disk') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ msg │ │ id │ │ │ └───────────────────────────┘ ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ contains(msg, 'disk') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ msg │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Combine it with a pushed-down condition.** In `id > 1 AND contains(msg, 'disk')`, `id > 1` is still evaluated in VAST DataBase, so `contains()` runs only on the rows that pass it. A time range or an ID range usually cuts the most rows. - **Store what you filter on.** If you often search messages for a few known words, such as a severity, write them to their own column when you load the rows: `severity = 'ERROR'` is pushed down to VAST DataBase. The plan for a raw column compared with a constant has no `FILTER` step: ``` plan(f"SELECT id FROM {TABLE} WHERE id > 1 AND contains(msg, 'disk')") plan(f"SELECT id FROM {TABLE} WHERE msg = 'ERROR disk full on /data'") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #0 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ contains(msg, 'disk') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ id │ │ msg │ │ │ └───────────────────────────┘ ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` - **`LIKE '%ab%'` and `strpos(s, 'ab') > 0` aren't faster.** `LIKE` becomes the same `contains()` call, and `strpos` also runs in the `FILTER` step. ## Limitations and errors - **Strings, lists and maps only.** A number, a date, a boolean, a `BLOB` or a struct fails with `No function matches the given name and argument types`; the error lists the three forms `contains` accepts. Cast a value to `VARCHAR` first to search its text. - **The element must match the list's type.** `contains([1, 2], 'll')` fails with `Cannot match element of type 'VARCHAR' in a list of type 'INTEGER[]' - an explicit cast is required`. - **Exact code points.** See [What it does](/develop/docs/dev-database-reference-sql-functions-string-contains#what-it-does) for case and accents. ``` run("SELECT contains(CAST(2026 AS VARCHAR), '02')") run("SELECT contains({'k': 1}, 'k')") ``` ``` [(True,)] InternalError: Binder Error: No function matches the given name and argument types 'contains(STRUCT(k INTEGER), STRING_LITERAL)'. You might need to add explicit type casts. ``` ## Similar functions | Function | Tests | Result for `'Hello'` and `'h'` | | --- | --- | --- | | `contains` | the substring, case-sensitive | `false` | | `contains(lower(s), ...)` | the substring, after lower-casing the string | `true` | | [ILIKE](/develop/docs/dev-database-reference-sql-functions-string-ilike) `'%h%'` | the same as a pattern, ignoring case | `true` | | [LIKE](/develop/docs/dev-database-reference-sql-functions-string-like) `'%h%'` | rewritten to `contains()` | `false` | | [strpos](/develop/docs/dev-database-reference-sql-functions-string-strpos) | where the substring first occurs (0 if it doesn't) | `0` | | [regexp_matches](/develop/docs/dev-database-reference-sql-functions-string-regexp-matches) | a regular expression anywhere in the string | `false` | | [list_contains](/develop/docs/dev-database-reference-sql-functions-lists-list-contains) | an element of a list | (lists only) | ``` run("SELECT contains('Hello', 'h'), contains(lower('Hello'), 'h'), 'Hello' ILIKE '%h%', 'Hello' LIKE '%h%', " "strpos('Hello', 'h'), regexp_matches('Hello', 'h')") run("SELECT contains(['a', 'b'], 'a'), list_contains(['a', 'b'], 'a'), contains('a,b', 'a')") ``` ``` [(False, True, True, False, 0, False)] [(True, True, True)] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Search and split](/develop/docs/dev-database-reference-sql-functions-string-search-and-split): all the search functions. - [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.