# Text search in MotherDuck


> Text search strategies from pattern matching to semantic search with embeddings in MotherDuck.

Text search is a fundamental operation in data analytics - whether you're finding records by name, searching documents for relevant content, or building question-answering systems. This guide covers search strategies available in MotherDuck, from simple pattern matching to advanced semantic search, and how to combine them for optimal results.

## Quick start: Common search patterns

Start here to identify the best search method for your use case. The right search approach depends on what you're searching, how you expect to use search, and what results you need. Most use cases fall into one of three patterns, each linking to detailed implementation guidance below:

**Keyword Search Over Identifiers**: When searching for specific items like company names, product codes, or customer names, use [exact match](#exact-match) for precise and low-latency lookups. If you need typo tolerance (e.g., "MotheDuck" → "MotherDuck"), use [fuzzy search](#fuzzy-search-text-similarity).

**Keyword Search Over Documents**: When searching longer text like articles, product descriptions, or documentation, use [full-text search](#full-text-search-fts). This ranks documents by keyword relevance, and handles cases where users provide a few keywords that should appear in the content.

**Semantic Search**: When searching by meaning and similarity rather than exact keywords, use [embedding-based search](#embedding-based-search). This covers:
- Understanding synonyms (e.g., matching "data warehouse" with "analytics platform")
- Understanding natural language queries (e.g., "wireless headphones with good battery life")
- Finding similar content (e.g., support tickets describing similar customer issues)

---

For answering natural language questions about *structured*  data (e.g., "How many customers do we have in California?"), see [Analytics Agents](/key-tasks/ai-and-motherduck/building-analytics-agents/).

## Refining your search strategy

If the patterns above don't fully match your use case, use these four questions to navigate to the right method. Each question links to specific sections with implementation details:

1. **What is the search corpus?** Consider what you're searching through:
   - **Identifiers** like company names, product IDs, or person names → [exact match](#exact-match) or [fuzzy search](#fuzzy-search-text-similarity)
   - **Documents** like articles, descriptions, or reports → [Keyword search (regex)](#exact-match) or [full-text search](#full-text-search-fts) (FTS) or [embedding-based search](#embedding-based-search) or [hybrid](#hybrid-search-with-rank-fusion) (combining FTS + embeddings)
   - **Structured (numerical) data** → [Analytics Agents](/key-tasks/ai-and-motherduck/building-analytics-agents/) that convert natural language questions to SQL

2. **What is the user input?** Think about how users express their search:
   - **Single terms** like "MotherDuck" → [exact match](#exact-match) or [fuzzy search](#fuzzy-search-text-similarity)
   - **Keyword phrases** like "data warehouse analytics" → [Keyword search (regex)](#exact-match) or [full-text search](#full-text-search-fts) or [embedding-based search](#embedding-based-search)
   - **Questions** like "What companies offer cloud analytics?" → [embedding-based search](#embedding-based-search) with [HyDE](#hypothetical-document-embeddings-hyde)
   - **Example documents** (finding similar content) → [embedding-based search](#embedding-based-search)

3. **What is the desired output?** Clarify what you're returning:
   - **Ranked list** (retrieval of documents/records) → Covered by this guide
   - **Generated text answers** (RAG-style Q&A, chatbots, summarization) → Use retrieval methods from this guide in combination with the [`prompt()`](/sql-reference/motherduck-sql-reference/ai-functions/prompt/#retrieval-augmented-generation-rag) function.

4. **What is the desired search behavior?** Think about what search qualities matter:
   - **Exact match** for specific words (IDs and codes) → [exact match](#exact-match) or [Keyword search (regex)](#using-regular-expressions)
   - **Typo resilience** to handle misspellings like "MotheDuck" → "MotherDuck" → [fuzzy search](#fuzzy-search-text-similarity)
   - **Synonym resilience** to match "data warehouse" with "analytics platform" → [embedding-based search](#embedding-based-search)
   - **Recall** so that neither a missing keyword nor a weak embedding match drops a document → [hybrid search with rank fusion](#hybrid-search-with-rank-fusion)
   - **Customizable ranking** → See [reranking](#reranking) in the [advanced methods](#advanced-methods) section
   - **Latency and concurrency** → See [performance guide](#performance-guide)

## Search methods

### Exact match

Use exact match search for specific identifiers, codes, or when you need guaranteed matches. This is the fastest search method.

#### Using LIKE

For substring matching, use `LIKE` (or `ILIKE` for case-insensitive). In patterns, `%` matches any sequence of characters and `_` matches exactly one character.

```sql
-- Find places with 'Starbucks' in their name
SELECT name, locality, region
FROM foursquare.main.fsq_os_places
WHERE name LIKE '%Starbucks%'
LIMIT 10;
```

See also: [Pattern Matching](https://duckdb.org/docs/stable/sql/functions/pattern_matching.html) in DuckDB documentation

#### Using regular expressions

For more complex pattern matching or matching multiple keywords, use `regexp_matches()` with `(?i)` for case-insensitive searches:

```sql
-- Find Hacker News posts with 'python', 'javascript', or 'rust' in text
SELECT title, "by", score
FROM sample_data.hn.hacker_news
WHERE regexp_matches(text, '(?i)(python|javascript|rust)')
LIMIT 10;
```

See also: [Regular Expressions](https://duckdb.org/docs/stable/sql/functions/regular_expressions) in DuckDB documentation

### Fuzzy search (text similarity)

Fuzzy search handles typos and spelling variations in entity names like companies, people, or products. Use `jaro_winkler_similarity()` for most fuzzy matching scenarios - it offers the best balance of accuracy and performance compared to `damerau_levenshtein()` or `levenshtein()`.

```sql
-- Find places similar to 'McDonalds' (handles typo 'McDonalsd')
SELECT
  name,
  locality,
  region,
  jaro_winkler_similarity('McDonalsd', name) AS similarity
FROM foursquare.main.fsq_os_places
ORDER BY similarity DESC
LIMIT 10;
```

See also: [Text Similarity Functions](https://duckdb.org/docs/stable/sql/functions/text#text-similarity-functions) in DuckDB documentation

### Full-text search (FTS)

Full-Text Search ranks documents by keyword relevance using BM25 scoring, which considers both how often terms appear in a document and how rare they are across all documents. Use this for articles, descriptions, or longer text where you need relevance ranking. FTS automatically handles word stemming (e.g., "running" matches "run") and removes common stopwords (like "the", "and", "or"), but requires exact word matches - it won't handle typos in search queries.

#### Basic FTS setup

FTS requires write access to the table. Since we're using a read-only example database, we first create a copy of the table in a read-write database we own:

```sql
CREATE TABLE hn_stories AS
SELECT id, title, text, "by", score, type
FROM sample_data.hn.hacker_news
WHERE type = 'story'
  AND LENGTH(text) > 100
LIMIT 10000;
```

Build the FTS index on the text column. This creates a new schema called `fts_{schema}_{table_name}` (in this case `fts_main_hn_stories`):

```sql
PRAGMA create_fts_index(
  'hn_stories',  -- table name
  'id',          -- document ID column
  'text'         -- text column to index
);
```

Search the index using the `match_bm25` function from the newly created schema:

```sql
SELECT
  id,
  title,
  text,
  fts_main_hn_stories.match_bm25(id, 'database analytics') AS score
FROM hn_stories
ORDER BY score DESC
LIMIT 10;
```

#### Index maintenance

FTS indexes need to be updated when the underlying data changes. Rebuild the index using the `overwrite` parameter:

```sql
PRAGMA create_fts_index('hn_stories', 'id', 'text', overwrite := 1);
```

See also: [Full-Text Search Guide](https://duckdb.org/docs/stable/guides/sql_features/full_text_search.html) and [Full-Text Search Extension](https://duckdb.org/docs/stable/core_extensions/full_text_search) in DuckDB documentation

### Embedding-based search

Embedding-based search finds conceptually similar text by meaning, not keywords. Use this for natural language queries, handling synonyms, or when users search with questions. Embeddings handle synonyms and typos naturally without manual configuration.

:::note
Embedding generation and lookups are priced in [AI Units](/about-motherduck/billing/pricing#advanced-ai-functions). For paid organizations, Business and Lite plans have a default soft limit of 10 AI Units per user/day (sufficient to embed around 600,000 rows) to help prevent unexpected costs. If you'd like to adjust these limits, [just ask!](/troubleshooting/support)
:::

:::info
The DuckDB [VSS extension](https://duckdb.org/docs/stable/core_extensions/vss) for approximate vector search (HNSW) is experimental and not supported in MotherDuck's cloud service (server-side). [Learn more](/concepts/duckdb-extensions/) about MotherDuck's support for DuckDB extensions. For an approximate nearest neighbor index that sits alongside MotherDuck rather than inside it, see [Lance and LanceDB](/integrations/file-formats/lance).
:::

#### Basic embedding-based search setup

Generate embeddings for your text data, then search using exact vector similarity. For search queries phrased as questions (like "What are the best practices for...?"), see [hypothetical document embeddings](#hypothetical-document-embeddings-hyde).

```sql
-- Reusing the hn_stories table from the FTS section, add embeddings
ALTER TABLE hn_stories ADD COLUMN text_embedding FLOAT[512];
UPDATE hn_stories SET text_embedding = embedding(text);

-- Semantic search - this will also match texts with related concepts like 'neural networks', 'deep learning', etc.
SELECT
  title,
  text,
  array_cosine_similarity(
    embedding('machine learning and artificial intelligence'),
    text_embedding
  ) AS similarity
FROM hn_stories
ORDER BY similarity DESC
LIMIT 10;
```

See also: [MotherDuck Embedding Function](/sql-reference/motherduck-sql-reference/ai-functions/embedding/), and [array_cosine_similarity](https://duckdb.org/docs/stable/sql/functions/array#array_cosine_similarityarray1-array2) in DuckDB documentation

#### Embedding search always returns rows

Vector similarity ranks every row it reads, so a query that matches nothing still comes back with a confidently ordered list. There is no empty result to signal "no match".

On the 10,000-story table above, the top similarity is 0.544 for `kubernetes` and 0.535 for `rust memory safety`. For the invented word `zblorptenna` it's 0.354 - lower, but not zero, and still attached to a ranked list of stories about nothing in particular. Full-text search over that same query returns no rows at all, because no document contains the word.

The floor that separates a real match from noise depends on your corpus and your embedding model, so measure it against your own data rather than copying a threshold. Where the search needs to be able to say "nothing matched", pair the embedding arm with a keyword arm - see [hybrid search with rank fusion](#hybrid-search-with-rank-fusion).

#### Document chunking for embedding-based search

When documents are longer than ~2000 characters, consider breaking them into smaller chunks to improve retrieval precision and focus results. For production pipelines with PDFs or Word docs, you can use the [MotherDuck integration for Unstructured.io](https://motherduck.com/blog/effortless-etl-unstructured-data-unstructuredio-motherduck/). Otherwise, you can also do document chunking in the database - here are some helpful macros:

```sql
-- Fixed-size chunking with configurable overlap
CREATE MACRO chunk_fixed_size(text_col, chunk_size, overlap) AS TABLE (
  SELECT
    gs.generate_series as chunk_number,
    substring(text_col, (gs.generate_series - 1) * (chunk_size - overlap) + 1, chunk_size) AS chunk_text
  FROM generate_series(1, CAST(CEIL(LENGTH(text_col) / (chunk_size - overlap * 1.0)) AS INTEGER)) gs
  WHERE LENGTH(substring(text_col, (gs.generate_series - 1) * (chunk_size - overlap) + 1, chunk_size)) > 50
);

-- Paragraph-based chunking (splits on double newlines)
CREATE MACRO chunk_paragraphs(text_col) AS TABLE (
  WITH chunks AS (SELECT string_split(text_col, '\n\n') as arr)
  SELECT
    UNNEST(generate_series(1, array_length(arr))) as chunk_number,
    UNNEST(arr) as chunk_text
  FROM chunks
);

-- Sentence-based chunking (splits on sentence boundaries)
CREATE MACRO chunk_sentences(text_col) AS TABLE (
  WITH chunks AS (SELECT string_split_regex(text_col, '[.!?]+\s+') as arr)
  SELECT
    UNNEST(generate_series(1, array_length(arr))) as chunk_number,
    UNNEST(arr) as chunk_text
  FROM chunks
);
```

Use one of the macros to create chunks from your documents. Fixed-size chunks (300-600 chars with 10-20% overlap) work well for most use cases:

```sql
CREATE OR REPLACE TABLE hn_text_chunks AS
SELECT
  id AS post_id,
  title,
  chunks.chunk_number,
  chunks.chunk_text
FROM hn_stories
CROSS JOIN LATERAL chunk_fixed_size(text, 500, 100) chunks;
-- Alternative: CROSS JOIN LATERAL chunk_paragraphs(text) chunks;
-- Alternative: CROSS JOIN LATERAL chunk_sentences(text) chunks;
```

Generate embeddings for the chunks:

```sql
ALTER TABLE hn_text_chunks ADD COLUMN chunk_embedding FLOAT[512];
UPDATE hn_text_chunks SET chunk_embedding = embedding(chunk_text);
```

Once you have chunks with embeddings, search them the same way as full documents using `array_cosine_similarity()` - the chunk-level results often provide more precise matches than searching entire documents.

## Performance guide

Search performance depends on several factors, from the chosen search method, to cold vs. warm reads, Duckling sizing, and tenancy model.

When running a search query against your data for the first time (cold read), it may have a higher latency than subsequent queries (warm reads). For production search workloads, ideally dedicate a service account's Duckling primarily to search, so other queries don't compete with search queries. Account for [Duckling cooldown periods](/about-motherduck/billing/duckling-sizes/) - the first search query after cooldown may experience more latency.

The DuckDB analytics engine divides data into chunks and processes them in parallel across threads. More data means more chunks to process in parallel, so larger datasets don't necessarily take proportionally longer to search - they just use more threads simultaneously.

**Duckling sizing:** Optimal latency requires warm reads and enough threads to process your data in parallel. With the ideal [Duckling sizing](/about-motherduck/billing/duckling-sizes/) configuration matched to your dataset size, keyword search over identifiers ([exact match](#exact-match), [fuzzy match](#fuzzy-search-text-similarity)) typically achieves latencies in the range of a few hundred milliseconds, while document search ([regex](#using-regular-expressions), [full-text search](#full-text-search-fts), [embedding search](#embedding-based-search)) typically achieves 0.5-3 second latency. Our team is happy to help advise on the right resource allocation for your specific workload and latency targets - [get in touch](/troubleshooting/support) to discuss how we can meet your needs.

**Handling Concurrent Requests:** For handling multiple simultaneous search requests effectively, consider using [read scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) to distribute load across multiple read scaling Ducklings. Alternatively, consider [hypertenancy](/concepts/hypertenancy), providing isolated compute resources for each user.

To optimize further, see the strategies below. For questions or requirements beyond this guide, please [get in touch](/troubleshooting/support).

### Search optimization strategies

When optimizing search performance, consider the following options.

#### Pre-filtering

Reduce the search space using structured metadata (e.g. location, categories, date ranges) that can be inferred from the user's context, before running similarity searches:

```sql
-- Create a local copy with embeddings for place names (using a subset)
CREATE TABLE places AS
SELECT fsq_place_id, name, locality, region, fsq_category_labels
FROM foursquare.main.fsq_os_places
WHERE name IS NOT NULL
LIMIT 10000;

-- Add embeddings for semantic search
ALTER TABLE places ADD COLUMN name_embedding FLOAT[512];
UPDATE places SET name_embedding = embedding(name);

-- Pre-filter by location before semantic search
WITH filtered_candidates AS (
  SELECT fsq_place_id, name, locality, fsq_category_labels, name_embedding
  FROM places
  WHERE locality = 'New York'  -- Filter by location and region
    AND region = 'NY'
)
SELECT
  name,
  locality,
  fsq_category_labels,
  array_cosine_similarity(
    embedding('italian restaurant'),
    name_embedding
  ) AS similarity
FROM filtered_candidates
ORDER BY similarity DESC
LIMIT 20;
```

#### Reducing embedding dimensionality

Halving embedding dimensions roughly halves compute time. OpenAI embeddings can be truncated at specific dimensions (256 for `text-embedding-3-small`, 256 or 512 for `text-embedding-3-large`). Use lower dimensions for initial pre-filtering, then rerank with full embeddings:

```sql
-- Setup: Create normalization macro
CREATE MACRO normalize(v) AS (
  CASE
    WHEN len(v) = 0 THEN NULL
    WHEN sqrt(list_dot_product(v, v)) = 0 THEN NULL
    ELSE list_transform(v, element -> element / sqrt(list_dot_product(v, v)))
  END
);

-- Add lower-dimensional column (e.g., 256 dims instead of 512)
ALTER TABLE hn_stories ADD COLUMN text_embedding_short FLOAT[256];
UPDATE hn_stories SET text_embedding_short = normalize(text_embedding[1:256]);
```

Then use a two-stage search:

```sql
-- Stage 1: Fast pre-filter with short embeddings
SET VARIABLE query_emb = embedding('machine learning algorithms', 'text-embedding-3-large');
SET VARIABLE query_emb_short = normalize(getvariable('query_emb')[1:256])::FLOAT[256];

WITH candidates AS (
  SELECT id,
    array_cosine_similarity(getvariable('query_emb_short'), text_embedding_short) AS similarity
  FROM hn_stories
  ORDER BY similarity DESC
  LIMIT 500  -- Get more candidates if needed
)
-- Stage 2: Rerank with full embeddings
SELECT p.title, p.text,
  array_cosine_similarity(getvariable('query_emb'), p.text_embedding) AS final_similarity
FROM hn_stories p
WHERE p.id IN (SELECT id FROM candidates)
ORDER BY final_similarity DESC
LIMIT 10;
```

#### FTS pre-filtering (hybrid search)

FTS typically has lower latency than embedding search, making it effective as a pre-filter to reduce similarity comparisons. Use a large LIMIT in the FTS stage to ensure good recall:

```sql
-- FTS pre-filter with large limit, then semantic rerank
SET VARIABLE search_query = 'artificial intelligence neural networks';

WITH fts_candidates AS (
  SELECT id,
    fts_main_hn_stories.match_bm25(id, getvariable('search_query')) AS fts_score
  FROM hn_stories
  ORDER BY fts_score DESC
  LIMIT 10000  -- Large limit to ensure recall
)
SELECT h.id, h.title, h.text,
  array_cosine_similarity(
    embedding(getvariable('search_query')),
    h.text_embedding
  ) AS similarity
FROM hn_stories h
INNER JOIN fts_candidates f ON h.id = f.id
ORDER BY similarity DESC
LIMIT 10;
```

This cascade optimizes for latency: the keyword arm narrows the candidates and the embedding arm does the ranking, so a document the keyword arm misses can't come back. To let each arm rescue the other's misses, see [hybrid search with rank fusion](#hybrid-search-with-rank-fusion).

See also: [Search Using DuckDB Part 3 (Hybrid Search)](https://motherduck.com/blog/search-using-duckdb-part-3/)

#### Moving the index out of MotherDuck

Every method on this page computes distances over the candidate rows as it reads them. With [pre-filtering](#pre-filtering) and [reduced dimensions](#reducing-embedding-dimensionality) doing the work, that holds up into the low millions of rows. Past roughly ten million vectors you want a real approximate nearest neighbor index, which MotherDuck doesn't have.

[Lance](/integrations/file-formats/lance) is the way out that keeps the rest of the stack intact. It holds the vectors and the index in object storage, answers the nearest neighbor query there, and hands back rows you join to your MotherDuck tables in the same statement.

## Advanced methods

This section covers additional techniques to customize and improve your search. The methods below demonstrate common approaches - many other variants are possible.

:::note
Some methods in this section make use of the `prompt()` function, which is priced in [AI Units](/about-motherduck/billing/pricing#advanced-ai-functions). For paid organizations, Business and Lite plans have a default soft limit of 10 AI Units per user/day (sufficient to process around 80,000 rows) to help prevent unexpected costs. If you'd like to adjust these limits, [just ask!](/troubleshooting/support)
:::

### LLM-enhanced keyword expansion

Generate synonyms with an LLM, then use them in pattern matching:

```sql
-- Generate synonyms using LLM with structured output
SET VARIABLE search_term = 'programming';

WITH synonyms AS (
  SELECT prompt(
    'Give me 5 synonyms for ''' || getvariable('search_term') || '''',
    struct := {'synonyms': 'VARCHAR[]'}
  ).synonyms AS synonym_list
)
-- Search with expanded terms
SELECT
  title,
  text
FROM sample_data.hn.hacker_news, synonyms
WHERE regexp_matches(text, getvariable('search_term') || '|' || array_to_string(synonym_list, '|'))
LIMIT 10;
```

See also: [MotherDuck `prompt()` Function](/sql-reference/motherduck-sql-reference/ai-functions/prompt/)

### Hypothetical document embeddings (HyDE)

HyDE improves question-based retrieval by generating a hypothetical answer first, then searching with that answer's embedding. This works because questions and answers have different linguistic patterns - the hypothetical answer better matches actual document content. Use with semantic search or the semantic component of hybrid search.

```sql
-- HyDE: Generate hypothetical answer, then search with it
WITH hypothetical_answer AS (
  SELECT prompt(
    'Answer this question in 2-3 sentences:
     "What are the key challenges in building scalable distributed systems?"

     Focus on typical technical challenges and solutions.'
  ) AS answer
)
-- Search using the hypothetical answer's embedding
SELECT
  title,
  text,
  array_cosine_similarity(
    (SELECT embedding(answer) FROM hypothetical_answer),
    text_embedding
  ) AS similarity
FROM hn_stories
ORDER BY similarity DESC
LIMIT 10;
```

See also: [Precise Zero-Shot Dense Retrieval without Relevance Labels (HyDE paper)](https://arxiv.org/abs/2212.10496)

### Hybrid search with rank fusion

Keyword search and embedding search fail in opposite directions. BM25 misses a document that never uses the query's words. Embedding search never misses, which is the same thing as saying it [returns rows for a query that matches nothing](#embedding-search-always-returns-rows). Running both covers each other's gap.

The catch is that their scores share no scale. A cosine similarity of 0.31 and a BM25 score of 8.4 mean nothing next to each other, and normalizing them is guesswork that shifts with every query. Reciprocal rank fusion sidesteps the problem by discarding the scores and combining rank positions instead. Each arm contributes `1 / (k + rank)` per document, where `k` damps how much the top few positions dominate; 60 is the conventional value:

```sql
SET VARIABLE search_query = 'kubernetes';
SET VARIABLE query_embedding = embedding(getvariable('search_query'));

WITH semantic AS (
  SELECT id, row_number() OVER (
      ORDER BY array_cosine_similarity(getvariable('query_embedding'), text_embedding) DESC
    ) AS rank
  FROM hn_stories
  ORDER BY rank
  LIMIT 100
),
keyword AS (
  SELECT id, row_number() OVER (ORDER BY score DESC) AS rank
  FROM (
    SELECT id, fts_main_hn_stories.match_bm25(id, getvariable('search_query')) AS score
    FROM hn_stories
  )
  WHERE score IS NOT NULL
  ORDER BY rank
  LIMIT 100
)
SELECT h.id, h.title, s.rank AS semantic_rank, k.rank AS keyword_rank,
  coalesce(1.0 / (60 + s.rank), 0) + coalesce(1.0 / (60 + k.rank), 0) AS rrf_score
FROM semantic AS s
FULL OUTER JOIN keyword AS k USING (id)
JOIN hn_stories AS h USING (id)
ORDER BY rrf_score DESC
LIMIT 10;
```

The `FULL OUTER JOIN` is what makes this hybrid rather than a filter: a document that only one arm found keeps that arm's share instead of dropping out, and a document both arms found beats either alone.

To weight the arms, multiply each term - `0.4 * coalesce(...) + 0.6 * coalesce(...)` leans towards the keyword arm. That's a reasonable default, because a keyword hit is evidence that the query's words appear in the document, while a semantic hit is present whether it means anything or not.

For the version tuned for latency rather than ranking quality, see [FTS pre-filtering](#fts-pre-filtering-hybrid-search), which uses the keyword arm to shrink the candidate set instead of to rank it.

### Reranking

Reranking typically happens in two stages: initial retrieval to get top candidates (100-500 results), then precise reranking of that smaller set.

#### Rule-based reranking with metadata

Refine results based on business rules and metadata like score, category, or freshness:

```sql
-- Find similar posts with metadata-based reranking
WITH initial_similarity AS (
  -- Step 1: Fast vector similarity for top candidates
  SELECT
    title,
    text,
    score as author_score,
    array_cosine_similarity(
      embedding('artificial intelligence and machine learning applications'),
      text_embedding
    ) AS emb_similarity
  FROM hn_stories
  ORDER BY emb_similarity DESC
  LIMIT 100
),
reranked_scores AS (
  -- Step 2: Rerank with metadata (author score)
  SELECT
    title,
    text,
    author_score,
    emb_similarity,
    -- Score boost (normalize to 0-1 range based on actual data)
    (author_score / MAX(author_score) OVER ()) AS author_score_norm,
    -- Combined final score: 60% semantic + 40% author score
    (emb_similarity * 0.6 + author_score_norm * 0.4) AS reranked_score
  FROM initial_similarity
)
SELECT
  title,
  text,
  author_score,
  ROUND(emb_similarity, 3) as semantic_score,
  ROUND(author_score_norm, 3) as author_score_normalized,
  ROUND(reranked_score, 3) as final_score
FROM reranked_scores
ORDER BY reranked_score DESC
LIMIT 10;
```

#### LLM-based reranking

For complex relevance criteria that are hard to express as rules, use an LLM to judge and score results. The [`prompt()` function](/sql-reference/motherduck-sql-reference/ai-functions/prompt/) is optimized for batch processing and processes requests in parallel - so reranking 50 results typically adds only a few hundred milliseconds.

```sql
-- LLM reranking for top search results
SET VARIABLE search_query = 'best practices for code review and software quality';

WITH top_candidates AS (
  -- Initial retrieval (e.g., via semantic search)
  SELECT
    id,
    title,
    text,
    array_cosine_similarity(
      embedding(getvariable('search_query')),
      text_embedding
    ) AS initial_score
  FROM hn_stories
  ORDER BY initial_score DESC
  LIMIT 20
),
llm_reranked AS (
  SELECT
    *,
    prompt(
      format(
        'Rate how well this post matches the query ''{}''.
         Post: {} - {}',
        getvariable('search_query'), title, text
      ),
      struct := {'rating': 'INTEGER'}
    ).rating AS llm_score
  FROM top_candidates
)
SELECT
  title,
  text,
  ROUND(initial_score, 3) as initial_score,
  llm_score,
  ROUND((0.6 * initial_score + 0.4 * llm_score / 10.0), 3) AS final_score
FROM llm_reranked
ORDER BY final_score DESC
LIMIT 10;
```

## Next steps

- Check out the MotherDuck [Embedding Function](/sql-reference/motherduck-sql-reference/ai-functions/embedding/) and [Prompt Function](/sql-reference/motherduck-sql-reference/ai-functions/prompt/)
- Review the [Full-Text Search Guide](https://duckdb.org/docs/stable/guides/sql_features/full_text_search.html) in DuckDB documentation
- Read the MotherDuck blog series: [Search Using DuckDB Part 1](https://motherduck.com/blog/search-using-duckdb-part-1/), [Part 2](https://motherduck.com/blog/search-using-duckdb-part-2/), [Part 3](https://motherduck.com/blog/search-using-duckdb-part-3/)
- Explore [Building Analytics Agents with MotherDuck](/key-tasks/ai-and-motherduck/building-analytics-agents/)
- Keep an approximate nearest neighbor index alongside MotherDuck with [Lance and LanceDB](/integrations/file-formats/lance)


---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fkey-tasks%2Fai-and-motherduck%2Ftext-search-in-motherduck%2F&page_title=Text%20Search%20in%20MotherDuck&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.