Skip to main content

Search in SQL

This section documents search capabilities in Spice SQL, including vector search, full-text search, and lexical filtering methods. These features help retrieve relevant data using semantic similarity, keyword matching, and pattern-based filtering.

Table of Contents​

Vector search retrieves records by semantic similarity using embeddings. It is ideal for finding related content even when exact keywords differ.

Usage​

SELECT id, score
FROM vector_search(table, 'search query')
ORDER BY score DESC
LIMIT 5;
  • table: Dataset name (required)
  • query: Search text (required)
  • col: Column name (optional if only one embedding column)
  • limit: Maximum results (optional)
  • include_score: Include relevance scores (optional, default TRUE)

Example​

SELECT review_id, rating, customer_id, body, score
FROM vector_search(reviews, 'issues with same day shipping')
WHERE created_at >= to_unixtime(now() - INTERVAL '7 days')
ORDER BY score DESC
LIMIT 2;

See Vector-Based Search for configuration and advanced usage.

Full-Text Search (text_search)​

Full-text search uses BM25 scoring to retrieve records matching keywords in indexed columns.

Usage​

SELECT id, score
FROM text_search(table, 'search terms', col)
ORDER BY score DESC
LIMIT 5;
  • table: Dataset name (required)
  • query: Keyword or phrase (required)
  • col: Column to search (required if multiple indexed columns)
  • limit: Maximum results (optional)
  • include_score: Include relevance scores (optional, default TRUE)

Example​

SELECT id, title, score
FROM text_search(doc.pulls, 'search keywords', body)
ORDER BY score DESC
LIMIT 5;

See Full-Text Search for configuration and details.

Lexical Search: LIKE, =, and Regex​

Spice SQL supports traditional filtering for exact and pattern-based matches:

LIKE (Pattern Matching)​

SELECT * FROM my_table WHERE column LIKE '%substring%';
  • % matches any sequence of characters.
  • _ matches a single character.

= (Keyword/Exact Match)​

SELECT * FROM my_table WHERE column = 'exact value';

Returns rows where the column exactly matches the value.

Regex Filtering​

Spice SQL does not support the ~ or !~ operators for regular expression matching. Instead, use scalar functions such as regexp_like, regexp_match, and regexp_replace for regex-based filtering. For details and examples, see the Scalar Functions documentation.

Example​

SELECT * FROM my_table WHERE regexp_like(column, '^spice.*ai$');

For more on hybrid and advanced search, see Search Functionality and Vector-Based Search.