Filtering with Text Search — Full-Text and Partial Match

In this tutorial, you will learn about Filtering with Text Search. We cover key concepts, practical examples, and best practices to help you master this topic.

Text search filtering lets clients find records matching search terms across multiple fields using case-insensitive partial matching or full-text search.

Request Format

GET /api/users?search=alice
GET /api/products?q=wireless+headphones

Implementation with ILIKE

app.get("/api/users", async (req, res) => {
  const { search } = req.query;
  const conditions = [];
  const params = [];
  let idx = 1;

  if (search) {
    conditions.push(
      `(name ILIKE $${idx} OR email ILIKE $${idx})`
    );
    params.push(`%${search}%`);
    idx++;
  }

  const where = conditions.length
    ? `WHERE ${conditions.join(" AND ")}`
    : "";
  const result = await db.query(`SELECT * FROM users ${where}`, params);
  res.json({ data: result.rows });
});

Full-Text Search (PostgreSQL)

-- Full-text search with tsvector
SELECT * FROM products
WHERE to_tsvector('english', name || ' ' || description)
  @@ plainto_tsquery('english', 'wireless headphones');

Common Mistakes

1. Case-sensitive search — Using LIKE instead of ILIKE in PostgreSQL.
2. SQL Injection — Concatenating search terms directly into queries.
3. No minimum length — Allowing single-character searches that return too many results.
4. No special character escaping — Search terms with percent or underscore break LIKE queries.

Practice Questions

1. How do you search across multiple fields?
2. What is the difference between ILIKE and LIKE?
3. Why use parameterized queries for search?
4. When should you use full-text search over ILIKE?

Challenge

Implement search for a documentation API. Search across title, content, and tags. Use ILIKE for basic search, add full-text search as an option.

What's Next

In the next lesson, you will learn complex filtering with AND/OR/nested conditions.