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

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

# hamming

*Applies to VAST Cluster 5.5.*

*[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Text basics](/develop/docs/dev-database-reference-sql-functions-string-text-basics) › Similarity.* Returns the Hamming distance between two strings of the same length in bytes: the number of byte positions at which they differ. `mismatches` is the same function.

## Syntax

```
hamming(a, b)
mismatches(a, b)
```

| Argument | Type | Description |
| --- | --- | --- |
| `a` | `VARCHAR` | The first string. |
| `b` | `VARCHAR` | The string to compare it with. It must have the same length as `a` in bytes. |

**Returns:** `BIGINT`.

**Aliases:** `mismatches`.

## 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_hamming`. 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 six-character part codes. Row 5 has a code of the wrong length, and the last row has no code:

```
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_hamming"'

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}.parts"
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, 'AB12CD'), "
    "(2, 'AB12CE'), "
    "(3, 'XB12CD'), "
    "(4, 'ZZ99ZZ'), "
    "(5, 'AB12'), "
    "(6, NULL)")
```

```
[]
[]
[(6,)]
```

## What it does

`hamming` lines the two strings up byte by byte and counts the positions where they differ. It never inserts or deletes, so the strings must have the same length; it suits fixed-length codes such as part numbers, postcodes or hashes.

| Call | Result | Why |
| --- | --- | --- |
| `hamming(&apos;karolin&apos;, &apos;kathrin&apos;)` | `3` | `r`/`t`, `o`/`h`, `l`/`r` differ |
| `hamming(&apos;kitten&apos;, &apos;sittin&apos;)` | `2` | `k`/`s` and `e`/`i` differ |
| `mismatches(&apos;abc&apos;, &apos;abd&apos;)` | `1` | the same function |
| `hamming(&apos;abc&apos;, &apos;ABC&apos;)` | `3` | the comparison is case-sensitive |
| `hamming(&apos;abc&apos;, &apos;ab&apos;)` | Error | the lengths differ |

**It compares UTF-8 bytes, not characters.** A letter outside ASCII takes two or more bytes, so `hamming(&apos;&#xe9;&apos;, &apos;e&apos;)` fails: the strings are one character each, but `&#xe9;` is two bytes and `e` is one. `hamming(&apos;&#xe9;&apos;, &apos;&#xe8;&apos;)` is `1`: both are two bytes, and only the second byte differs. A string with an accented letter has more bytes than characters: `&apos;caf&#xe9;&apos;`, with a precomposed `&#xe9;`, is five bytes, so it can be compared with `&apos;Hallo&apos;`, and the distance is `4`. Check lengths with [strlen](/develop/docs/dev-database-reference-sql-functions-string-strlen) (bytes), not [length](/develop/docs/dev-database-reference-sql-functions-string-length) (characters).

**One bad row stops the whole query.** If any row's strings have different lengths, or both are empty, the query fails with the error and returns no rows, including the rows that would have worked. A NULL argument gives NULL, not the error. To run `hamming` on a column, keep only the rows of the right length first ([Examples](/develop/docs/dev-database-reference-sql-functions-string-hamming#examples) shows how).

## NULLs and edge cases

| Input | Result |
| --- | --- |
| `NULL` as either argument | `NULL` |
| `&apos;karolin&apos;` and `&apos;kathrin&apos;` | `3` |
| `&apos;abc&apos;` and `&apos;ABC&apos;` | `3` |
| `&apos;&apos;` and `&apos;Hallo&apos;` | Error: `Mismatch Function: Strings must be of equal length!` |
| `&apos;abc&apos;` and `&apos;ab&apos;` | Error: `Mismatch Function: Strings must be of equal length!` |
| `&apos;&#xe9;&apos;` and `&apos;e&apos;` | Error: `Mismatch Function: Strings must be of equal length!` |
| `&apos;&apos;` and `&apos;&apos;` | Error: `Mismatch Function: Strings must be of length &gt; 0!` |
| A number, decimal, date, boolean, `BLOB` or list | 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-hamming#set-up-the-examples):

```
run("SELECT hamming(NULL, 'Hallo'), hamming('a', NULL), hamming('karolin', 'kathrin'), "
    "hamming('abc', 'ABC'), mismatches('abc', 'abd')")
run("SELECT hamming('', 'Hallo')")
run("SELECT hamming('abc', 'ab')")
run("SELECT hamming('\u00e9', 'e')")
run("SELECT hamming('', '')")
run("SELECT hamming(123, 'Hallo')")
```

```
[(None, None, 3, 3, 1)]
OSError: Mismatch Function: Strings must be of equal length!
OSError: Mismatch Function: Strings must be of equal length!
OSError: Mismatch Function: Strings must be of equal length!
OSError: Mismatch Function: Strings must be of length > 0!
InternalError: Binder Error: No function matches the given name and argument types 'hamming(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
```

## Examples

On the whole column, the query fails because of row 5, and returns nothing:

```
run(f"SELECT id, code, hamming(code, 'AB12CD') FROM {TABLE} ORDER BY id LIMIT 10")
```

```
OSError: Mismatch Function: Strings must be of equal length!
```

Keep only the six-byte codes in `WHERE`, so `hamming()` in the `SELECT` list runs on the rows that remain:

```
run(f"SELECT id, code, hamming(code, 'AB12CD') FROM {TABLE} "
    "WHERE strlen(code) = 6 ORDER BY id LIMIT 10")
```

```
[(1, 'AB12CD', 0), (2, 'AB12CE', 1), (3, 'XB12CD', 1), (4, 'ZZ99ZZ', 6)]
```

To filter on the distance itself, put the length check and `hamming()` in a `CASE`, so `hamming()` is called only when the lengths match. This keeps the codes that differ from `AB12CD` in at most one position:

```
run(f"SELECT id, code FROM {TABLE} "
    "WHERE CASE WHEN strlen(code) = 6 THEN hamming(code, 'AB12CD') <= 1 ELSE false END "
    "ORDER BY id LIMIT 10")
```

```
[(1, 'AB12CD'), (2, 'AB12CE'), (3, 'XB12CD')]
```

## 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 `hamming()` is always computed by the Query Engine:

- **In the `SELECT` list,** the scan reads the column and the Query Engine computes `hamming()` for each row it returns.
- **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step in the Query Engine computes `hamming()` and keeps the rows that match. No rows are skipped in storage.

The plan of a distance filter shows the `FILTER` step above the scan:

```
plan(f"SELECT id FROM {TABLE} WHERE hamming(code, 'AB12CD') <= 1")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (hamming(code, 'AB12CD') <│
│            = 1)           │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            code           │
│             id            │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Store codes of one length, and their length.** If you store the byte length in its own column when you write the rows, `code_bytes = 6` is a comparison of a raw column with a constant, which is evaluated in VAST DataBase: rows of other lengths never leave storage and never reach `hamming()`.
- **Look up exact matches without it.** `code = &apos;AB12CD&apos;` is pushed down; `hamming(code, &apos;AB12CD&apos;) = 0` gives the same rows but runs on every row (and fails on rows of other lengths). The plan for `code = &apos;AB12CD&apos;` has no `FILTER` step:

```
plan(f"SELECT id FROM {TABLE} WHERE code = 'AB12CD'")
```

```
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
```

## Limitations and errors

- **Same length only.** Strings of different lengths fail with `Mismatch Function: Strings must be of equal length!`, and two empty strings with `Mismatch Function: Strings must be of length &gt; 0!`. On a column, one such row stops the whole query. For strings of different lengths, use [levenshtein](/develop/docs/dev-database-reference-sql-functions-string-levenshtein).
- **Bytes, not characters.** Lengths are compared in bytes, and so are positions. See [What it does](/develop/docs/dev-database-reference-sql-functions-string-hamming#what-it-does).
- **Strings only.** A number, decimal, date, boolean, `BLOB` or list fails with `No function matches the given name and argument types`. Cast it first: `hamming(CAST(n AS VARCHAR), &apos;123&apos;)`.
- **Case-sensitive.** Compare `lower()` of both strings to ignore case.

```
run(f"SELECT mismatches(code, 'AB12CD') FROM {TABLE} WHERE id = 5")
run("SELECT hamming(123, 'Hallo')")
```

```
OSError: Mismatch Function: Strings must be of equal length!
InternalError: Binder Error: No function matches the given name and argument types 'hamming(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
```

## Similar functions

| Function | Measures | Result for `&apos;kitten&apos;` and `&apos;sittin&apos;` (`hamming` gives `2`) |
| --- | --- | --- |
| [levenshtein](/develop/docs/dev-database-reference-sql-functions-string-levenshtein) (also `editdist3`) | edit distance with insertions and deletions; any lengths | `levenshtein(&apos;kitten&apos;, &apos;sitting&apos;)` gives `3` |
| [damerau_levenshtein](/develop/docs/dev-database-reference-sql-functions-string-damerau-levenshtein) | edit distance where swapping two neighbours is one edit | `damerau_levenshtein(&apos;kitten&apos;, &apos;sitting&apos;)` gives `3` |
| [jaccard](/develop/docs/dev-database-reference-sql-functions-string-jaccard) | distinct bytes the strings share, as a share of all their distinct bytes, from 0 to 1 | `jaccard(&apos;kitten&apos;, &apos;sitting&apos;)` gives `0.42857142857142855` |
| [jaro_similarity](/develop/docs/dev-database-reference-sql-functions-string-jaro-similarity) | similarity from 0 to 1, based on matching bytes and transpositions | `jaro_similarity(&apos;kitten&apos;, &apos;sitting&apos;)` gives `0.746031746031746` |

The other functions accept strings of different lengths, so they're compared on `&apos;kitten&apos;` and `&apos;sitting&apos;`, which `hamming` can't compare.

```
run("SELECT hamming('kitten', 'sittin'), levenshtein('kitten', 'sitting'), "
    "damerau_levenshtein('kitten', 'sitting'), jaccard('kitten', 'sitting'), jaro_similarity('kitten', 'sitting')")
run("SELECT hamming('kitten', 'sitting')")
```

```
[(2, 3, 3, 0.42857142857142855, 0.746031746031746)]
OSError: Mismatch Function: Strings must be of equal length!
```

Clean up:

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

```
[]
[]
```

## See also

- [Text basics](/develop/docs/dev-database-reference-sql-functions-string-text-basics): all the text 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.
- [levenshtein](/develop/docs/dev-database-reference-sql-functions-string-levenshtein) and [strlen](/develop/docs/dev-database-reference-sql-functions-string-strlen).