MATCH

MATCH searches for rows that contain the supplied keywords within the listed columns. The function can only appear in a WHERE clause.

Syntax

MATCH('<columns>', '<keywords>'[, '<options>'])

• <columns>: A comma-separated list of columns to search. Append ^<boost> to weight a column higher than the others.
• <keywords>: The terms to search for. Append * for suffix matching, for example rust*.
• <options>: An optional semicolon-separated list of key=value pairs fine-tuning the search.

Options

Examples

In many AI pipelines you may capture structured metadata in a VARIANT column while also materializing human-readable summaries for search. The following example stores dashcam frame summaries and tags that were extracted from the JSON payload.

Example: Build Searchable Summaries

CREATE OR REPLACE TABLE frame_notes (
  id INT,
  camera STRING,
  summary STRING,
  tags STRING,
  INVERTED INDEX idx_notes (summary, tags)
);

INSERT INTO frame_notes VALUES
  (1, 'dashcam_front',
      'Green light at Market & 5th with pedestrian entering the crosswalk',
      'downtown commute green-light pedestrian'),
  (2, 'dashcam_front',
      'Vehicle stopped at Mission & 6th red traffic light with cyclist ahead',
      'stop urban red-light cyclist'),
  (3, 'dashcam_front',
      'School zone caution sign in SOMA with pedestrian waiting near crosswalk',
      'school-zone caution pedestrian');

Example: Boolean AND

SELECT id, summary
FROM frame_notes
WHERE MATCH('summary, tags', 'traffic light red', 'operator=AND');
-- Returns id 2

Example: Fuzzy Matching

SELECT id, summary
FROM frame_notes
WHERE MATCH('summary^2, tags', 'pedestrain', 'fuzziness=1');
-- Returns ids 1 and 3