---
title: "starts_with"
slug: "dev-database-reference-sql-functions-string-starts-with"
updated: 2026-10-30T18:18:56Z
published: 2026-10-30T18:18:56Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-starts-with"
---

> ## 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.

# starts_with

*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 begins with a given prefix. `prefix` and the `^@` operator are the same test, and the Query Engine rewrites `LIKE 'ab%'` to it.

## Syntax

```
starts_with(s, prefix)
prefix(s, prefix)
s ^@ prefix
```

| Argument | Type | Description |
| --- | --- | --- |
| `s` | `VARCHAR` | The string to test. |
| `prefix` | `VARCHAR` | The text `s` must begin with. |

**Returns:** `BOOLEAN`.

**Aliases:** `prefix`, `^@`.

## 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_starts_with`. 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 files. 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_starts_with"'

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}.files"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, path VARCHAR, note VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '/logs/app/2026-10-13.log', 'Disk full'), "
    "(2, '/logs/db/2026-10-12.log', 'caf\u00e9 menu'), "
    "(3, '/tmp/load_50%.csv', 'cafe\u0301 menu'), "
    "(4, NULL, NULL)")
```

```
[]
[]
[(4,)]
```

## What it does

`starts_with(s, prefix)` is true when the first characters of `s` are exactly `prefix`. The comparison is code point by code point:

- **Case matters.** `starts_with(&apos;Abc&apos;, &apos;a&apos;)` 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) `&apos;ab%&apos;`.
- **Wildcards are plain characters.** `%`, `_` and `*` match only themselves: `starts_with(&apos;a%c&apos;, &apos;a%&apos;)` is true.
- **Accents must be written the same way.** A precomposed `&#xe9;` (U+00E9) and `e` followed by U+0301 look the same but are different code points, so `starts_with(&apos;&#xe9;&apos;, &apos;e\u0301&apos;)` is false, while `starts_with(&apos;e\u0301x&apos;, &apos;e&apos;)` 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 prefix matches every string,** including the empty string.

## NULLs and edge cases

| Input | Result |
| --- | --- |
| `starts_with(NULL, &apos;He&apos;)`, `starts_with(&apos;abc&apos;, NULL)` | `NULL` |
| `starts_with(&apos;abc&apos;, &apos;&apos;)`, `starts_with(&apos;&apos;, &apos;&apos;)` | `true` |
| `starts_with(&apos;&apos;, &apos;a&apos;)` | `false` |
| `starts_with(&apos;abc&apos;, &apos;abcd&apos;)` (prefix longer than the string) | `false` |
| `starts_with(&apos;Abc&apos;, &apos;a&apos;)` | `false` |
| `starts_with(&apos; abc&apos;, &apos;a&apos;)` (leading space) | `false` |
| `starts_with(&apos;a%c&apos;, &apos;a%&apos;)`, `starts_with(&apos;a_c&apos;, &apos;a_&apos;)` | `true` |
| `starts_with(&apos;e\u0301x&apos;, &apos;e&apos;)` (`e` and a combining accent) | `true` |
| `starts_with(&apos;&#xe9;&apos;, &apos;e\u0301&apos;)` (precomposed `&#xe9;`, decomposed prefix) | `false` |
| `starts_with(&apos;&#x1f1eb;&#x1f1f7;x&apos;, &apos;&#x1f1eb;&apos;)` (half of a flag emoji) | `true` |
| A number, date, boolean, `BLOB` or list on either side | 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-starts-with#set-up-the-examples):

```
run("SELECT starts_with(NULL, 'He'), starts_with('abc', NULL), starts_with('abc', ''), starts_with('', ''), "
    "starts_with('', 'a'), starts_with('abc', 'abcd')")
run("SELECT starts_with('Abc', 'a'), starts_with(' abc', 'a'), starts_with('a%c', 'a%'), starts_with('a_c', 'a_')")
run("SELECT starts_with('e\u0301x', 'e'), starts_with('\u00e9', 'e\u0301'), starts_with('\U0001F1EB\U0001F1F7x', '\U0001F1EB')")
run("SELECT starts_with(123, 'He')")
```

```
[(None, None, True, True, False, False)]
[(False, False, True, True)]
[(True, False, True)]
InternalError: Binder Error: No function matches the given name and argument types 'starts_with(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
```

## Examples

Test each path with all three spellings. The row with no path gives `NULL`:

```
run(f"SELECT id, starts_with(path, '/logs/'), prefix(path, '/tmp'), path ^@ '/logs/db' FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, True, False, False), (2, True, False, True), (3, False, True, False), (4, None, None, None)]
```

Keep the rows under `/logs/`:

```
run(f"SELECT id, path FROM {TABLE} WHERE starts_with(path, '/logs/') ORDER BY id LIMIT 10")
```

```
[(1, '/logs/app/2026-10-13.log'), (2, '/logs/db/2026-10-12.log')]
```

Rows 2 and 3 both start with `caf&#xe9;` on screen, but only row 2 matches a precomposed prefix until the column is normalised:

```
run(f"SELECT id, starts_with(note, 'caf\u00e9'), starts_with(nfc_normalize(note), 'caf\u00e9') FROM {TABLE} "
    "ORDER BY id LIMIT 10")
```

```
[(1, False, False), (2, True, True), (3, False, True), (4, None, 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 `starts_with()` is always computed by the Query Engine:

- **In the `SELECT` list,** the scan reads the column and the Query Engine computes `starts_with()` 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 &apos;ab%&apos;` and `regexp_matches(s, &apos;^ab&apos;)` are rewritten to `prefix(s, &apos;ab&apos;)`, so they run the same way. The plan of the `WHERE` example shows the `FILTER` step above the scan:

```
plan(f"SELECT id FROM {TABLE} WHERE starts_with(path, '/logs/')")
plan(f"SELECT id FROM {TABLE} WHERE path LIKE '/logs/%'")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│starts_with(path, '/logs/')│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   prefix(path, '/logs/')  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Use a range instead of a prefix test.** `path &gt;= &apos;/logs/&apos; AND path &lt; &apos;/logs0&apos;` selects the strings that start with `/logs/`, and it's pushed down to VAST DataBase, so rows that don't match never leave storage. The upper bound is the prefix with its last character replaced by the next one (`/` is followed by `0`). The plan has no `FILTER` step:

```
plan(f"SELECT id FROM {TABLE} WHERE path >= '/logs/' AND path < '/logs0'")
run(f"SELECT id FROM {TABLE} WHERE path >= '/logs/' AND path < '/logs0' ORDER BY id LIMIT 10")
```

```
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
[(1,), (2,)]
```

- **Combine it with a pushed-down condition.** In `id &gt; 1 AND starts_with(path, &apos;/logs/&apos;)`, `id &gt; 1` is still evaluated in VAST DataBase, so `starts_with()` runs only on the rows that pass it.
- **Sort the table on the searched column.** A range on a sorted column lets the scan skip whole blocks; see [indexing and data skipping](/develop/docs/dev-database-guides-indexing-and-data-skipping).

## Limitations and errors

- **Strings only.** A number, a date, a boolean, a `BLOB` or a list on either side fails with `No function matches the given name and argument types`, for example `&apos;starts_with(INTEGER_LITERAL, STRING_LITERAL)&apos;`. Cast the value to `VARCHAR` first.
- **No `startswith`.** The name without the underscore fails with `Scalar Function with name startswith does not exist!`. Use `starts_with`.
- **Exact code points.** See [What it does](/develop/docs/dev-database-reference-sql-functions-string-starts-with#what-it-does) for case and accents.

```
run("SELECT starts_with(CAST(123 AS VARCHAR), '12')")
run("SELECT startswith('abc', 'a')")
```

```
[(True,)]
InternalError: Catalog Error: Scalar Function with name startswith does not exist!
```

## Similar functions

| Function | Tests | Where it runs in `WHERE` |
| --- | --- | --- |
| `starts_with`, `prefix`, `^@` | the beginning of the string | `FILTER` step |
| [LIKE](/develop/docs/dev-database-reference-sql-functions-string-like) `&apos;He%&apos;` | the same; rewritten to `prefix()` | `FILTER` step |
| [regexp_matches](/develop/docs/dev-database-reference-sql-functions-string-regexp-matches) `(s, &apos;^He&apos;)` | the same; rewritten to `prefix()` | `FILTER` step |
| `s &gt;= &apos;He&apos; AND s &lt; &apos;Hf&apos;` | the same strings, as a range | pushed down |
| [ends_with](/develop/docs/dev-database-reference-sql-functions-string-ends-with) | the end of the string | `FILTER` step |
| [contains](/develop/docs/dev-database-reference-sql-functions-string-contains) | anywhere in the string | `FILTER` step |
| [strpos](/develop/docs/dev-database-reference-sql-functions-string-strpos) | where the text first occurs (a position, not a boolean) | `FILTER` step |

```
run("SELECT starts_with('Hello World', 'He'), prefix('Hello World', 'He'), 'Hello World' ^@ 'He', "
    "'Hello World' LIKE 'He%', regexp_matches('Hello World', '^He'), "
    "'Hello World' >= 'He' AND 'Hello World' < 'Hf'")
run("SELECT ends_with('Hello World', 'ld'), contains('Hello World', 'o W'), strpos('Hello World', 'o')")
```

```
[(True, True, True, True, True, True)]
[(True, True, 5)]
```

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.