---
title: "SIMILAR TO"
slug: "dev-database-reference-sql-functions-string-similar-to"
updated: 2026-10-30T18:18:56Z
published: 2026-10-30T18:18:56Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-string-similar-to"
---

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

# SIMILAR TO

*Applies to VAST Cluster 5.5.*

*[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Regular expressions](/develop/docs/dev-database-reference-sql-functions-string-regular-expressions) › Pattern matching.* `s SIMILAR TO pattern` is true if the whole string matches a regular expression. It's the operator form of [regexp_full_match](/develop/docs/dev-database-reference-sql-functions-string-regexp-full-match), and `s NOT SIMILAR TO pattern` returns the opposite. The pattern is a regular expression, not a [LIKE](/develop/docs/dev-database-reference-sql-functions-string-like) pattern: `.` and `.*` are the wildcards, and `%` and `_` are ordinary characters.

## Syntax

```
s SIMILAR TO pattern
s NOT SIMILAR TO pattern
```

| Argument | Type | Description |
| --- | --- | --- |
| `s` | `VARCHAR` | The string to test. |
| `pattern` | `VARCHAR` | A regular expression ([syntax](/develop/docs/dev-database-reference-sql-functions-string-regexp-syntax)). It must match the whole string. It can be a constant or a column. |

**Returns:** `BOOLEAN`.

**Related:** `s SIMILAR TO pattern` runs as `regexp_full_match(s, pattern)`, and `s NOT SIMILAR TO pattern` as `NOT regexp_full_match(s, pattern)`. There's no `similar_to()` function.

## 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_similar_to`. 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: a level, a date and a message. Row 3 has its level in lower 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_string_similar_to"'

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}.logs"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, line VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'ERROR 2026-10-16 disk full'), "
    "(2, 'INFO 2026-10-16 job started'), "
    "(3, 'error 2026-10-17 timeout'), "
    "(4, NULL)")
```

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

## What it does

`SIMILAR TO` tests whether the regular expression matches the **whole** string, as if it started with `^` and ended with `&#x24;`. To match anywhere in the string, put `.*` at both ends, or use [regexp_matches](/develop/docs/dev-database-reference-sql-functions-string-regexp-matches).

| Test | Result | Why |
| --- | --- | --- |
| `&apos;abc&apos; SIMILAR TO &apos;abc&apos;` | `true` | the whole string |
| `&apos;abc&apos; SIMILAR TO &apos;b&apos;` | `false` | `b` is only part of the string |
| `&apos;abc&apos; SIMILAR TO &apos;.*b.*&apos;` | `true` | `.*` matches the rest |
| `&apos;abc&apos; SIMILAR TO &apos;a.c&apos;` | `true` | `.` matches one character |
| `&apos;abc&apos; SIMILAR TO &apos;(a\\|x)bc&apos;`, `&apos;abc&apos; SIMILAR TO &apos;[a-c]+&apos;` | `true` | groups, alternatives and classes work |
| `&apos;abc&apos; SIMILAR TO &apos;a%&apos;`, `&apos;abc&apos; SIMILAR TO &apos;a_c&apos;` | `false` | `%` and `_` are ordinary characters |
| `&apos;a_c&apos; SIMILAR TO &apos;a_c&apos;` | `true` | a literal `_` |

- **Case counts.** `&apos;ABC&apos; SIMILAR TO &apos;abc&apos;` is false. Start the pattern with `(?i)` to ignore case: `&apos;ABC&apos; SIMILAR TO &apos;(?i)abc&apos;` is true.
- **`.` doesn't match a newline.** `&apos;a\nb&apos; SIMILAR TO &apos;a.b&apos;` is false. Start the pattern with `(?s)` to let `.` match one.
- **`.` matches one character outside ASCII,** such as a precomposed `&#xe9;`.
- **The pattern can be a column.** Each row is tested against its own pattern.

## NULLs and edge cases

| Input | Result |
| --- | --- |
| `s` is NULL | `NULL` |
| `pattern` is NULL | `NULL` |
| `&apos;&apos; SIMILAR TO &apos;&apos;`, `&apos;&apos; SIMILAR TO &apos;.*&apos;` | `true` |
| `&apos;&#xe9;&apos; SIMILAR TO &apos;.&apos;` (precomposed) | `true` |
| `&apos;a\nb&apos; SIMILAR TO &apos;a.b&apos;` (a newline in the string) | `false` |
| `NOT SIMILAR TO` with a NULL on either side | `NULL` |
| `&apos;abc&apos; NOT SIMILAR TO &apos;a.c&apos;`, `&apos;abc&apos; NOT SIMILAR TO &apos;b&apos;` | `false`, `true` |
| An invalid pattern such as `&apos;(&apos;` | Error: `Invalid Input Error: missing ): (` |
| `ESCAPE` after the pattern | Error: `Not implemented Error: Custom escape in SIMILAR TO` |
| `s` a number, `BLOB` or list, or a number as the pattern | Error: `No function matches the given name and argument types &apos;regexp_full_match(...)&apos;` |

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-similar-to#set-up-the-examples):

```
run("SELECT NULL SIMILAR TO 'a', 'abc' SIMILAR TO NULL, '' SIMILAR TO '', '' SIMILAR TO '.*', "
    "'\u00e9' SIMILAR TO '.', 'a\nb' SIMILAR TO 'a.b', 'a\nb' SIMILAR TO '(?s)a.b'")
run("SELECT 'abc' SIMILAR TO 'abc', 'abc' SIMILAR TO 'b', 'abc' SIMILAR TO '.*b.*', 'abc' SIMILAR TO 'a.c'")
run("SELECT 'abc' SIMILAR TO '(a|x)bc', 'abc' SIMILAR TO '[a-c]+', 'abc' SIMILAR TO 'a%', 'abc' SIMILAR TO 'a_c', "
    "'a_c' SIMILAR TO 'a_c'")
run("SELECT 'ABC' SIMILAR TO 'abc', 'ABC' SIMILAR TO '(?i)abc'")
run("SELECT 'abc' NOT SIMILAR TO NULL, 'abc' NOT SIMILAR TO 'a.c', 'abc' NOT SIMILAR TO 'b'")
run("SELECT 'abc' SIMILAR TO '('")
run("SELECT 'abc' SIMILAR TO 'a.*' ESCAPE '$'")
run("SELECT 123 SIMILAR TO '1.*'")
```

```
[(None, None, True, True, True, False, True)]
[(True, False, True, True)]
[(True, True, False, False, True)]
[(False, True)]
[(None, False, True)]
InternalError: Invalid Input Error: missing ): (
InternalError: Not implemented Error: Custom escape in SIMILAR TO
InternalError: Binder Error: No function matches the given name and argument types 'regexp_full_match(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
```

## Examples

Which lines are errors, case-sensitively and ignoring case, and which have the shape *level, date, message*. In Python, `&apos;\\d&apos;` is the SQL text `\d`:

```
run(f"SELECT id, line SIMILAR TO 'ERROR .*', line SIMILAR TO '(?i)error .*', "
    f"line SIMILAR TO '[A-Za-z]+ \\d+-\\d+-\\d+ .*' FROM {TABLE} ORDER BY id LIMIT 10")
```

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

Keep the lines that aren't errors in any case:

```
run(f"SELECT id, line FROM {TABLE} WHERE line NOT SIMILAR TO '(?i)error .*' ORDER BY id LIMIT 10")
```

```
[(2, 'INFO 2026-10-16 job started')]
```

## 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 regular expression, so `SIMILAR TO` is always computed by the Query Engine, as `regexp_full_match`:

- **In the `SELECT` list,** the scan reads the column and the Query Engine tests 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.
- **A pattern with a fixed beginning,** such as `&apos;ERROR .*&apos;`, gets a second `FILTER` step below the pattern test: a range test on the column, which skips the pattern test for rows that can't match. It runs in the Query Engine too, not in the scan.

The plan of a pattern with a fixed beginning shows both `FILTER` steps:

```
plan(f"SELECT id FROM {TABLE} WHERE line SIMILAR TO 'ERROR .*'")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  regexp_full_match(line,  │
│        'ERROR .*')        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ((line >= 'ERROR '::BLOB) │
│  AND (line <= 'ERROR \xF4 │
│   \xBF\xBF\xC0'::BLOB))   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            line           │
│             id            │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Use a plain comparison when you can.** A range on the raw column, such as `line &gt;= &apos;ERROR &apos; AND line &lt; &apos;ERROR!&apos;`, is pushed down to VAST DataBase, so rows that don't match never leave storage; no regular expression is. The plan of the range has no `FILTER` step.
- **Store the part you filter on.** If you often filter on the level, store it in its own column when you write the rows, and compare that column with a constant.
- **Combine it with a pushed-down condition.** In `id &gt; 1 AND line SIMILAR TO &apos;(?i)error .*&apos;`, `id &gt; 1` is still evaluated in VAST DataBase, so the pattern runs only on the rows that pass it.

```
plan(f"SELECT id FROM {TABLE} WHERE line >= 'ERROR ' AND line < 'ERROR!'")
run(f"SELECT id FROM {TABLE} WHERE line >= 'ERROR ' AND line < 'ERROR!' ORDER BY id LIMIT 10")
```

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

## Limitations and errors

- **Regular expressions only.** `%` and `_` aren't wildcards in `SIMILAR TO`; use [LIKE](/develop/docs/dev-database-reference-sql-functions-string-like) for those.
- **No `ESCAPE`.** `SIMILAR TO ... ESCAPE` fails with `Not implemented Error: Custom escape in SIMILAR TO`. Escape a special character in the pattern with a backslash instead, such as `&apos;a\.c&apos;`.
- **An invalid pattern fails.** A pattern such as `&apos;(&apos;` fails with `Invalid Input Error: missing ): (`. See [regular expression syntax](/develop/docs/dev-database-reference-sql-functions-string-regexp-syntax) for what a pattern can contain.
- **`VARCHAR` only.** A number, `BLOB` or list fails with `No function matches the given name and argument types &apos;regexp_full_match(INTEGER_LITERAL, STRING_LITERAL)&apos;` (with the type you passed). Cast it to `VARCHAR` first.
- **No `similar_to()` function.** It fails with `Scalar Function with name similar_to does not exist!`. Use `SIMILAR TO` or [regexp_full_match](/develop/docs/dev-database-reference-sql-functions-string-regexp-full-match).

```
run("SELECT 'a.c' SIMILAR TO 'a\\.c', 'abc' SIMILAR TO 'a\\.c'")
run("SELECT CAST(123 AS VARCHAR) SIMILAR TO '1.*'")
run("SELECT similar_to('abc', 'a.c')")
```

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

## Similar functions

| Function | Matches | Pattern | Result for `&apos;xabcx&apos;` and `&apos;abc&apos;` |
| --- | --- | --- | --- |
| `s SIMILAR TO &apos;abc&apos;` | the whole string | regular expression | `false` |
| [regexp_full_match](/develop/docs/dev-database-reference-sql-functions-string-regexp-full-match) `(s, &apos;abc&apos;)` | the whole string | regular expression | `false` |
| [regexp_matches](/develop/docs/dev-database-reference-sql-functions-string-regexp-matches) `(s, &apos;abc&apos;)` | anywhere in the string | regular expression | `true` |
| [LIKE](/develop/docs/dev-database-reference-sql-functions-string-like) `&apos;%abc%&apos;` | the whole string | `%` and `_` | `true` |
| [contains](/develop/docs/dev-database-reference-sql-functions-string-contains) `(s, &apos;abc&apos;)` | anywhere in the string | literal text | `true` |

```
run("SELECT 'xabcx' SIMILAR TO 'abc', regexp_full_match('xabcx', 'abc'), regexp_matches('xabcx', 'abc'), "
    "'xabcx' LIKE '%abc%', contains('xabcx', 'abc')")
run("SELECT 'ABC' SIMILAR TO '(?i)a.*', regexp_full_match('ABC', 'a.*', 'i')")
```

```
[(False, False, True, True, True)]
[(True, True)]
```

Clean up:

```
run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()
```

```
[]
[]
```

## See also

- [Regular expressions](/develop/docs/dev-database-reference-sql-functions-string-regular-expressions): all the regular expression functions.
- [regexp_full_match](/develop/docs/dev-database-reference-sql-functions-string-regexp-full-match), [regexp_matches](/develop/docs/dev-database-reference-sql-functions-string-regexp-matches) and [regular expression syntax](/develop/docs/dev-database-reference-sql-functions-string-regexp-syntax).
- [Operators and predicates](/develop/docs/dev-database-reference-sql-functions-operators-and-predicates#pattern-matching).
- [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.