SQL
Implementing Basic Full-Text Search in SQL
Add powerful search capabilities to your web application by querying text columns efficiently with database-native full-text search features.
-- MySQL / MariaDB Syntax (requires FULLTEXT index on 'title', 'content')
SELECT
id,
title,
content,
MATCH(title, content) AGAINST('search term' IN NATURAL LANGUAGE MODE) AS relevance_score
FROM
articles
WHERE
MATCH(title, content) AGAINST('search term' IN NATURAL LANGUAGE MODE)
ORDER BY
relevance_score DESC;
-- PostgreSQL Syntax (requires 'pg_trgm' or 'fuzzystrmatch' for similarity,
-- or 'tsvector' for proper FTS)
-- First, create a GIN index: CREATE INDEX articles_search_idx ON articles USING GIN(to_tsvector('english', title || ' ' || content));
SELECT
id,
title,
content,
ts_rank(to_tsvector('english', title || ' ' || content), to_tsquery('english', 'search & term')) AS relevance_score
FROM
articles
WHERE
to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'search & term')
ORDER BY
relevance_score DESC;
How it works: Full-text search allows you to perform sophisticated text-based queries on your database, going beyond simple `LIKE` comparisons. This snippet illustrates how to implement basic full-text search using both MySQL's `MATCH AGAINST` and PostgreSQL's `ts_vector`/`ts_query` functions. It enables searching across multiple text columns (e.g., `title` and `content`) and ranks results by relevance, providing more meaningful search results for users.