Full-Text Search Function in SQL

Searching for specific text within large amounts of data is a common need in modern applications. SQL provides a Full-Text Search (FTS) functionality to efficiently handle such searches. Unlike basic LIKE queries, which scan entire tables, full-text search uses specialized indexes and algorithms to deliver fast and accurate results for text-based data. This chapter provides a comprehensive guide, covering the basics to advanced concepts, with examples to demonstrate the capabilities of full-text search in SQL.

Understanding Full-Text Search

What is Full-Text Search?

Full-text search is a technique that enables the retrieval of documents or records that match complex textual queries. It is optimized for:

  • Searching large text columns.
  • Handling linguistic nuances like stemming and stop words.
  • Ranking results based on relevance.

Why Use Full-Text Search?

  • Faster than basic pattern matching (e.g., LIKE).
  • Supports advanced search capabilities, such as Boolean queries, phrase matching, and proximity searches.
  • Provides relevance scoring to rank results.

Supported Databases

Popular databases that support full-text search:

  • MySQL: Using Full-Text Indexes.
  • PostgreSQL: Through tsvector and tsquery.
  • SQL Server: Integrated Full-Text Search Service.

Setting Up Full-Text Search

Full-Text Index Basics

A full-text index is a special type of index optimized for text searches. It preprocesses data into tokens for efficient retrieval.

Key Features:

  • Handles large text columns (TEXT, VARCHAR, CHAR).
  • Ignores common stop words (e.g., “the”, “and”).
  • Provides linguistic processing (e.g., stemming).

Creating Full-Text Indexes in MySQL

  1. Enable Full-Text Index: Full-text search is supported for InnoDB and MyISAM storage engines.

  2. Create a Table with Full-Text Index:

				
					CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT(title, content)
);

FULLTEXT creates the index on title and content.

Populate the Table:

				
					INSERT INTO articles (title, content)
VALUES 
('SQL Basics', 'Learn the basics of SQL and relational databases.'),
('Advanced SQL', 'Discover advanced SQL techniques and performance optimization.');

Creating Full-Text Indexes in PostgreSQL

  1. Prepare the Text Columns: PostgreSQL uses tsvector to store searchable text.

				
					CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector tsvector
);

Populate the search_vector:

				
					UPDATE documents 
SET search_vector = to_tsvector(title || ' ' || content);

Create an Index:

				
					CREATE INDEX idx_search ON documents USING GIN(search_vector);

Creating Full-Text Indexes in SQL Server

  1. Enable Full-Text Search: Full-Text Search must be installed and enabled on your SQL Server instance.

  2. Create a Table:

				
					CREATE TABLE books (
    id INT PRIMARY KEY,
    title NVARCHAR(255),
    description NVARCHAR(MAX)
);

Create Full-Text Catalog and Index:

				
					CREATE FULLTEXT CATALOG BookCatalog AS DEFAULT;

CREATE FULLTEXT INDEX ON books(title, description)
KEY INDEX PK_books ON BookCatalog;

Performing Full-Text Searches

Basic Search in MySQL

Use the MATCH() and AGAINST() functions for full-text queries.

Example:

				
					SELECT * 
FROM articles
WHERE MATCH(title, content) AGAINST('SQL');

Finds all articles where the title or content contains “SQL”.

Boolean Search in MySQL

Boolean mode allows complex queries with operators (+, -, *, etc.).

Example:

				
					SELECT * 
FROM articles
WHERE MATCH(title, content) AGAINST('+SQL -Basics' IN BOOLEAN MODE);
  • +SQL: Must include “SQL”.
  • -Basics: Must not include “Basics”.

Searching in PostgreSQL

Use the @@ operator to match queries against a tsvector.

Example:

				
					SELECT * 
FROM documents
WHERE search_vector @@ to_tsquery('SQL & Basics');

Matches documents containing both “SQL” and “Basics”.

 Searching in SQL Server

Use the CONTAINS or FREETEXT functions.

Example (CONTAINS):

				
					SELECT * 
FROM books
WHERE CONTAINS(description, 'SQL AND Basics');

Example (FREETEXT):

				
					SELECT * 
FROM books
WHERE FREETEXT(description, 'learn SQL basics');

Advanced Full-Text Search Features

Ranking Results by Relevance

Databases rank results based on their relevance to the query.

Example in MySQL:

				
					SELECT title, MATCH(title, content) AGAINST('SQL') AS relevance
FROM articles
ORDER BY relevance DESC;

Highlighting Search Terms

Some databases allow highlighting matched terms.

Example in PostgreSQL:

				
					SELECT ts_headline(content, to_tsquery('SQL')) AS highlighted_content
FROM documents
WHERE search_vector @@ to_tsquery('SQL');

Proximity Search

Search for terms within a certain distance of each other.

Example in SQL Server:

				
					SELECT * 
FROM books
WHERE CONTAINS(description, 'NEAR((SQL, Basics), 5)');

Limitations and Best Practices

Limitations

  • Full-text indexes consume additional storage.
  • Performance can degrade for frequent updates or deletes.
  • Not ideal for small datasets or exact matching.

Best Practices

  • Use full-text search for large text columns with frequent read operations.
  • Regularly update and maintain indexes for optimal performance.
  • Use appropriate stop-word lists and linguistic rules.

Full-text search is a powerful tool for handling complex text-based queries in SQL databases. By leveraging full-text indexes, you can achieve faster and more accurate search results, especially for large datasets. This chapter has covered the fundamentals, practical implementations, and advanced features of full-text search, equipping you with the knowledge to use this feature effectively in real-world applications. Happy coding !❤️

Table of Contents

Popular Tutorials

Contact here

Copyright © 2025 Diginode

Made with ❤️ in India

Home
Roadmap
ATS Resume
Playground
Resources