The problem LIKE does not solve

You have a screen with a customer grid. The user starts typing “João Si” and expects to see “João Silva”, “João Silveira”, “João Simões”. Fast. While typing.

The most immediate solution is to use LIKE:

1
SELECT * FROM Clientes WHERE Nome LIKE '%João Si%'

It works. For 100 records. For 500 thousand records in production, with several users typing at the same time, that LIKE '%...%' is a full table scan — the database reads every row of the table, one by one, without using an index. The query gets slow, the database suffers, and the user abandons the screen.

Full-Text Search (FTS) solves exactly that: an indexing and text search mechanism specially designed for text queries, with support for:

  • Search by words and exact phrases
  • Relevance and result ranking
  • Word stems (stemming): “running” finds “run”
  • Synonyms and stopwords
  • Logical operators: AND, OR, NOT, NEAR
  • Inverted indexes — the database does not read every row, it reads the index

In this article I will implement the complete scenario: an Angular frontend with paginated reactive search that calls a C# REST API using Full-Text Search in the database — with examples for SQL Server, PostgreSQL and Oracle.

📦 Source code: The complete implementation of this article is in the blog-zocateli-sample repository on GitHub. Also see the Angular (search-ui) examples and the SQL scripts.


The scenario: Angular + REST API + Database via FTS

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
┌─────────────────────────────────────────────────────────────┐
│  Angular Grid (frontend)                                    │
│                                                             │
│  ┌─────────────────────────────────────────────┐            │
│  │     Type the customer name...               │            │
│  └─────────────────────────────────────────────┘            │
│       ↓ debounce 400ms + minimum 3 characters               │
│  ┌──────────────────────────────────────────────────────┐   │
│  │  Name          | CPF         | Email                 │   │
│  │  João Silva    | 123.456.789 | [email protected]  │   │
│  │  João Silveira | 987.654.321 | [email protected]  │   │
│  └──────────────────────────────────────────────────────┘   │
│  Page 1 of 12  [< Previous] [Next >]                        │
└─────────────────────────────────────────────────────────────┘
              │ GET /api/clientes?q=João+Si&pagina=1&tamanho=20
              ▼
┌─────────────────────────────────────────────────────────────┐
│  ASP.NET Core API                                           │
│  ClientesController → ClienteService → IClienteRepository   │
└─────────────────────────────────────────────────────────────┘
              │ paginated Full-Text Search query
              ▼
┌──────────────┐   ┌──────────────┐   ┌──────────────┐
│  SQL Server  │   │  PostgreSQL  │   │    Oracle    │
│  CONTAINS()  │   │  to_tsquery  │   │  CONTAINS()  │
│  FT Index    │   │  GIN Index   │   │  CTX Domain  │
└──────────────┘   └──────────────┘   └──────────────┘

Angular frontend: reactive search with debounce

Why a SPA?

The “search while typing” pattern is native to SPA frameworks (Single-Page Applications) such as Angular, React and Vue. In these frameworks, all interaction logic runs in the browser, and the reactive programming model — with RxJS in Angular — provides the debounceTime, distinctUntilChanged and switchMap operators directly, without hacks.

In server-side frameworks (Razor Pages, Blazor Server, Thymeleaf, JSF…), the rendering flow is different: each interaction must go to the server and return updated HTML. To implement the same behavior, you depend on additional JavaScript (either handwritten or via HTMX, Turbo, Alpine.js, etc.) to capture the typing event, apply the debounce on the client and fire the asynchronous request — adding a layer of complexity that does not exist in the SPA model. The result works, but development is more laborious and maintenance more fragmented (logic split between the server and loose JS scripts).

If your project already uses a server-side framework, evaluate whether it is worth introducing an island of SPA interactivity (Angular Element, isolated React) just for this search component, or whether a lightweight JS script such as HTMX is enough for the use case.

Angular with RxJS makes the “search while typing” pattern elegant. The idea is simple: do not fire a request on every key press — that would overload the server. I use two filters:

  1. Minimum of 3 characters — avoid searches with very short text
  2. 400ms debounce — wait for the user to stop typing before sending

Search component

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
// clientes-grid.component.ts
import { Component, OnInit, OnDestroy } from '@angular/core';
import { FormControl } from '@angular/forms';
import { Subject } from 'rxjs';
import {
  debounceTime,
  distinctUntilChanged,
  filter,
  switchMap,
  takeUntil
} from 'rxjs/operators';
import { ClienteService } from './cliente.service';
import { PagedResult, ClienteDto } from './models';

@Component({
  selector: 'app-clientes-grid',
  templateUrl: './clientes-grid.component.html'
})
export class ClientesGridComponent implements OnInit, OnDestroy {
  buscaControl = new FormControl('');
  clientes: ClienteDto[] = [];
  paginaAtual = 1;
  totalPaginas = 1;
  totalRegistros = 0;
  carregando = false;
  termoBusca = '';

  private destroy$ = new Subject<void>();

  constructor(private clienteService: ClienteService) {}

  ngOnInit(): void {
    this.buscaControl.valueChanges.pipe(
      debounceTime(400),                    // waits 400ms after the last key
      distinctUntilChanged(),               // ignores it if the value did not change
      filter(termo => !termo || termo.length === 0 || termo.length >= 3),
      takeUntil(this.destroy$)
    ).subscribe(termo => {
      this.termoBusca = termo ?? '';
      this.paginaAtual = 1;                 // goes back to the first page on each new search
      this.buscar();
    });

    // Loads the first page without a filter when the screen opens
    this.buscar();
  }

  buscar(): void {
    this.carregando = true;
    this.clienteService
      .listar(this.termoBusca, this.paginaAtual, 20)
      .pipe(takeUntil(this.destroy$))
      .subscribe({
        next: (resultado) => {
          this.clientes       = resultado.itens;
          this.totalPaginas   = resultado.totalPaginas;
          this.totalRegistros = resultado.totalRegistros;
          this.carregando     = false;
        },
        error: () => {
          this.carregando = false;
        }
      });
  }

  irParaPagina(pagina: number): void {
    if (pagina < 1 || pagina > this.totalPaginas) return;
    this.paginaAtual = pagina;
    this.buscar();
  }

  ngOnDestroy(): void {
    this.destroy$.next();
    this.destroy$.complete();
  }
}

HTML template

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
<!-- clientes-grid.component.html -->
<div class="grid-container">
  <div class="search-bar">
    <input
      [formControl]="buscaControl"
      placeholder="🔍 Type the customer name..."
      class="input-busca"
    />
    <span *ngIf="carregando" class="spinner">Searching...</span>
  </div>

  <table class="grid-tabela">
    <thead>
      <tr>
        <th>Name</th>
        <th>CPF/CNPJ</th>
        <th>Email</th>
        <th>City</th>
      </tr>
    </thead>
    <tbody>
      <tr *ngFor="let cliente of clientes">
        <td>{{ cliente.nome }}</td>
        <td>{{ cliente.cpfCnpj }}</td>
        <td>{{ cliente.email }}</td>
        <td>{{ cliente.cidade }}</td>
      </tr>
      <tr *ngIf="!carregando && clientes.length === 0">
        <td colspan="4" class="sem-resultado">No customers found.</td>
      </tr>
    </tbody>
  </table>

  <div class="paginacao">
    <span>{{ totalRegistros }} records | Page {{ paginaAtual }} of {{ totalPaginas }}</span>
    <button (click)="irParaPagina(paginaAtual - 1)" [disabled]="paginaAtual <= 1">‹ Previous</button>
    <button (click)="irParaPagina(paginaAtual + 1)" [disabled]="paginaAtual >= totalPaginas">Next ›</button>
  </div>
</div>

Angular service

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// cliente.service.ts
import { Injectable } from '@angular/core';
import { HttpClient, HttpParams } from '@angular/common/http';
import { Observable } from 'rxjs';
import { PagedResult, ClienteDto } from './models';

@Injectable({ providedIn: 'root' })
export class ClienteService {
  private readonly apiUrl = '/api/clientes';

  constructor(private http: HttpClient) {}

  listar(
    q: string,
    pagina: number,
    tamanho: number
  ): Observable<PagedResult<ClienteDto>> {
    let params = new HttpParams()
      .set('pagina', pagina)
      .set('tamanho', tamanho);

    if (q?.trim()) {
      params = params.set('q', q.trim());
    }

    return this.http.get<PagedResult<ClienteDto>>(this.apiUrl, { params });
  }
}
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
// models.ts
export interface ClienteDto {
  id: number;
  nome: string;
  cpfCnpj: string;
  email: string;
  cidade: string;
}

export interface PagedResult<T> {
  itens: T[];
  totalRegistros: number;
  totalPaginas: number;
  paginaAtual: number;
  tamanhoPagina: number;
}

Why switchMap? switchMap cancels the previous request when a new one arrives. If the user types quickly and the server has not yet answered the first request, the second one cancels the first — with no risk of results arriving out of order.


REST API with ASP.NET Core

Controller

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
// ClientesController.cs
[ApiController]
[Route("api/clientes")]
public class ClientesController(IClienteService service) : ControllerBase
{
    [HttpGet]
    public async Task<ActionResult<PagedResult<ClienteDto>>> Listar(
        [FromQuery] string? q,
        [FromQuery] int pagina = 1,
        [FromQuery] int tamanho = 20,
        CancellationToken ct = default)
    {
        if (tamanho > 100) tamanho = 100;  // maximum limit for safety
        if (pagina < 1)    pagina = 1;

        var resultado = await service.ListarAsync(q, pagina, tamanho, ct);
        return Ok(resultado);
    }
}

Models and DTOs

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
// PagedResult.cs
public record PagedResult<T>(
    IReadOnlyList<T> Itens,
    int TotalRegistros,
    int TotalPaginas,
    int PaginaAtual,
    int TamanhoPagina);

// ClienteDto.cs
public record ClienteDto(
    int Id,
    string Nome,
    string CpfCnpj,
    string Email,
    string Cidade);

// Cliente.cs (entity)
public class Cliente
{
    public int    Id        { get; set; }
    public string Nome      { get; set; } = "";
    public string CpfCnpj  { get; set; } = "";
    public string Email     { get; set; } = "";
    public string Cidade    { get; set; } = "";
}

Service

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
// ClienteService.cs
public class ClienteService(IClienteRepository repo) : IClienteService
{
    public async Task<PagedResult<ClienteDto>> ListarAsync(
        string? q, int pagina, int tamanho, CancellationToken ct)
    {
        var (itens, total) = await repo.BuscarAsync(q, pagina, tamanho, ct);

        return new PagedResult<ClienteDto>(
            Itens:         itens.Select(c => new ClienteDto(c.Id, c.Nome, c.CpfCnpj, c.Email, c.Cidade)).ToList(),
            TotalRegistros: total,
            TotalPaginas:   (int)Math.Ceiling((double)total / tamanho),
            PaginaAtual:    pagina,
            TamanhoPagina:  tamanho);
    }
}

What the Full-Text Index is in SQL Server

SQL Server maintains a full-text catalog — an inverted index structure separate from the normal B-Tree indexes. It tokenizes the text of the indexed columns, applies stopwords (ignored words: “de”, “do”, “a”…) and stemming (word stem reduction) per language, and stores the tokens with their positions in the documents.

The search uses the CONTAINS and FREETEXT predicates:

  • CONTAINS(coluna, '"João Silva"') — literal or prefix search
  • CONTAINS(coluna, '"João*"') — prefix (João, Joãozinho, etc.)
  • FREETEXT(coluna, 'João Silva') — natural language, more tolerant

Prerequisite: enabling Full-Text Search in SQL Server

Full-Text Search is an optional feature of SQL Server — it must be installed on the instance before any CREATE FULLTEXT INDEX. In standard installations of SQL Server Developer, Express with Advanced Services or Enterprise it is already active; in minimal installations it may be missing.

Check whether it is active:

1
2
-- Returns 1 if the feature is installed, 0 if it is not
SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS FTInstalled;

If it returns 0, you need to add the feature via SQL Server Setup:

  1. Open the SQL Server Installation Center (setup.exe from the installation media)
  2. Choose “Add features to an existing instance”
  3. Select “Full-Text and Semantic Extractions for Search”
  4. Complete the wizard — there is no need to restart the instance

In addition, the auxiliary service SQL Full-text Filter Daemon Launcher (MSSQLFDLauncher) must be running. Check it in SQL Server Configuration Manager or via PowerShell:

1
Get-Service -Name 'MSSQLFDLauncher*'

Complete reference: Get started with Full-Text Search — Microsoft Learn

Full-Text Index setup

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
-- 1. Create the catalog (once per database)
CREATE FULLTEXT CATALOG catalogo_clientes AS DEFAULT;

-- 2. Create the FT index on the Clientes table
--    Requires the table to have a unique key column (usually the PK)
CREATE FULLTEXT INDEX ON Clientes
(
    Nome          LANGUAGE 'Brazilian',
    Email         LANGUAGE 'Brazilian',
    Cidade        LANGUAGE 'Brazilian'
)
KEY INDEX PK_Clientes
ON catalogo_clientes
WITH STOPLIST = SYSTEM;

-- 3. Check indexing progress (may take a while the first time)
SELECT name, is_enabled, crawl_status_description
FROM sys.fulltext_indexes fi
JOIN sys.tables t ON fi.object_id = t.object_id;

Repository — SQL Server

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
// ClienteRepositorioSqlServer.cs
public class ClienteRepositorioSqlServer(AppDbContext db) : IClienteRepository
{
    public async Task<(IReadOnlyList<Cliente> itens, int total)> BuscarAsync(
        string? q, int pagina, int tamanho, CancellationToken ct)
    {
        // No filter: simple pagination
        if (string.IsNullOrWhiteSpace(q))
            return await BuscarSemFiltroAsync(pagina, tamanho, ct);

        var termo = FormatarTermoSqlServer(q);
        var offset = (pagina - 1) * tamanho;

        // CONTAINS with prefix — supports partial search at the start of words
        var sql = """
            SELECT Id, Nome, CpfCnpj, Email, Cidade
            FROM Clientes
            WHERE CONTAINS((Nome, Email, Cidade), {0})
            ORDER BY Nome
            OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY
            """;

        var countSql = """
            SELECT COUNT(*)
            FROM Clientes
            WHERE CONTAINS((Nome, Email, Cidade), {0})
            """;

        var itens = await db.Clientes
            .FromSqlRaw(sql, termo, offset, tamanho)
            .AsNoTracking()
            .ToListAsync(ct);

        var total = await db.Database
            .SqlQueryRaw<int>(countSql, termo)
            .FirstAsync(ct);

        return (itens, total);
    }

    private static string FormatarTermoSqlServer(string q)
    {
        // Splits into words and uses the "*" prefix for partial search:
        // "João Si" → '"João*" AND "Si*"'
        var palavras = q.Trim()
            .Split(' ', StringSplitOptions.RemoveEmptyEntries)
            .Select(p => $'"{EscaparSqlServer(p)}*"');

        return string.Join(" AND ", palavras);
    }

    private static string EscaparSqlServer(string palavra)
        // Prevents injection in the CONTAINS predicate
        => palavra.Replace("\"", "\"\"").Replace("'", "''");

    private async Task<(IReadOnlyList<Cliente>, int)> BuscarSemFiltroAsync(
        int pagina, int tamanho, CancellationToken ct)
    {
        var offset = (pagina - 1) * tamanho;
        var itens = await db.Clientes
            .AsNoTracking()
            .OrderBy(c => c.Nome)
            .Skip(offset)
            .Take(tamanho)
            .ToListAsync(ct);

        var total = await db.Clientes.CountAsync(ct);
        return (itens, total);
    }
}

Result example

For the term “João Si”, SQL Server internally executes:

1
2
3
4
5
6
7
8
9
-- Equivalent to what the FT engine executes:
SELECT Id, Nome, CpfCnpj, Email, Cidade
FROM Clientes
WHERE CONTAINS((Nome, Email, Cidade), '"João*" AND "Si*"')
ORDER BY Nome
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

-- Returns: João Silva, João Silveira, João Simões, João Simplício...
-- Using the inverted index — no full table scan

Relevance ranking with SQL Server

When ordering by relevance matters, use CONTAINSTABLE or FREETEXTTABLE, which return a RANK column:

1
2
3
4
5
6
7
8
var rankSql = """
    SELECT c.Id, c.Nome, c.CpfCnpj, c.Email, c.Cidade
    FROM Clientes c
    INNER JOIN CONTAINSTABLE(Clientes, (Nome, Email, Cidade), {0}) AS ft
        ON c.Id = ft.[KEY]
    ORDER BY ft.[RANK] DESC, c.Nome
    OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY
    """;

PostgreSQL: Full-Text Search with tsvector

How FTS works in PostgreSQL

PostgreSQL has native FTS support without additional extensions. The approach uses:

  • tsvector — normalized representation of the indexed text (tokens + positions)
  • tsquery — compiled search expression
  • to_tsvector(idioma, texto) — converts text to tsvector
  • to_tsquery(idioma, query) — converts a search to tsquery
  • websearch_to_tsquery — more tolerant, accepts user input directly
  • GIN index — efficient inverted index for tsvector

Prerequisite: enabling FTS in PostgreSQL

PostgreSQL has included native Full-Text Search support since version 8.3 — there is no additional component to install and no separate license. The feature is available in any standard installation, including RDS, Azure Database for PostgreSQL and Cloud SQL.

Check which language configurations are available:

1
2
3
4
5
-- Lists the installed text search dictionaries
SELECT cfgname, cfgparser::regproc
FROM pg_ts_config
ORDER BY cfgname;
-- Must contain 'portuguese' for use in Portuguese

For Portuguese text with accents, it is recommended to enable the unaccent extension, which normalizes accented characters during indexing (“João” and “Joao” become equivalent):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- Extension included in PostgreSQL contrib — no external dependency
CREATE EXTENSION IF NOT EXISTS unaccent;

-- Variant of the portuguese dictionary that ignores accents:
CREATE TEXT SEARCH CONFIGURATION portuguese_unaccent (COPY = portuguese);
ALTER TEXT SEARCH CONFIGURATION portuguese_unaccent
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, portuguese_stem;

-- Use 'portuguese_unaccent' instead of 'portuguese' in the to_tsvector / to_tsquery functions

Complete reference: Full-Text Search — PostgreSQL Docs

FTS index setup

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 1. Add a tsvector column for indexing (alternative: compute on-the-fly)
ALTER TABLE clientes ADD COLUMN busca_fts TSVECTOR;

-- 2. Populate the column with the concatenated texts (different weights per field)
UPDATE clientes SET busca_fts =
    setweight(to_tsvector('portuguese', coalesce(nome, '')),   'A') ||
    setweight(to_tsvector('portuguese', coalesce(email, '')),  'B') ||
    setweight(to_tsvector('portuguese', coalesce(cidade, '')), 'C');

-- 3. Keep the column updated automatically via trigger
CREATE OR REPLACE FUNCTION atualizar_busca_fts()
RETURNS TRIGGER AS $$
BEGIN
    NEW.busca_fts :=
        setweight(to_tsvector('portuguese', coalesce(NEW.nome,   '')), 'A') ||
        setweight(to_tsvector('portuguese', coalesce(NEW.email,  '')), 'B') ||
        setweight(to_tsvector('portuguese', coalesce(NEW.cidade, '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_clientes_busca_fts
BEFORE INSERT OR UPDATE ON clientes
FOR EACH ROW EXECUTE FUNCTION atualizar_busca_fts();

-- 4. Create a GIN index (optimized for tsvector)
CREATE INDEX idx_clientes_busca_fts ON clientes USING GIN (busca_fts);

setweight and weights A, B, C, D: they let you give priority to fields. Results where the term appears in the name (A) get a higher ranking than where it appears in the city (C). The ranking is calculated by the ts_rank function.

Repository — PostgreSQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
// ClienteRepositorioPostgres.cs
public class ClienteRepositorioPostgres(AppDbContext db) : IClienteRepository
{
    public async Task<(IReadOnlyList<Cliente> itens, int total)> BuscarAsync(
        string? q, int pagina, int tamanho, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(q))
            return await BuscarSemFiltroAsync(pagina, tamanho, ct);

        var offset = (pagina - 1) * tamanho;

        // websearch_to_tsquery: accepts human input directly — safer
        // Converts "João Si" into 'João:* & Si:*' internally
        // The :* clause indicates a prefix (partial search)
        var sql = """
            SELECT id, nome, cpf_cnpj, email, cidade
            FROM clientes
            WHERE busca_fts @@ websearch_to_tsquery('portuguese', {0})
            ORDER BY ts_rank(busca_fts, websearch_to_tsquery('portuguese', {0})) DESC,
                     nome
            LIMIT {1} OFFSET {2}
            """;

        var countSql = """
            SELECT COUNT(*)
            FROM clientes
            WHERE busca_fts @@ websearch_to_tsquery('portuguese', {0})
            """;

        var itens = await db.Clientes
            .FromSqlRaw(sql, q.Trim(), tamanho, offset)
            .AsNoTracking()
            .ToListAsync(ct);

        var total = await db.Database
            .SqlQueryRaw<int>(countSql, q.Trim())
            .FirstAsync(ct);

        return (itens, total);
    }

    private async Task<(IReadOnlyList<Cliente>, int)> BuscarSemFiltroAsync(
        int pagina, int tamanho, CancellationToken ct)
    {
        var offset = (pagina - 1) * tamanho;
        var itens = await db.Clientes
            .AsNoTracking()
            .OrderBy(c => c.Nome)
            .Skip(offset)
            .Take(tamanho)
            .ToListAsync(ct);

        var total = await db.Clientes.CountAsync(ct);
        return (itens, total);
    }
}

Prefix search in PostgreSQL

websearch_to_tsquery does not support prefixes natively. For search while typing where each word must be treated as a prefix, use to_tsquery with :*:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
private static string FormatarTermoPostgres(string q)
{
    // "João Si" → "'João':* & 'Si':*"
    var palavras = q.Trim()
        .Split(' ', StringSplitOptions.RemoveEmptyEntries)
        .Select(p => $"'{EscaparPostgres(p)}':*");

    return string.Join(" & ", palavras);
}

private static string EscaparPostgres(string p)
    => p.Replace("'", "''").Replace("\\", "\\\\");
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
// Usage with to_tsquery for prefix:
var sql = """
    SELECT id, nome, cpf_cnpj, email, cidade
    FROM clientes
    WHERE busca_fts @@ to_tsquery('portuguese', {0})
    ORDER BY ts_rank(busca_fts, to_tsquery('portuguese', {0})) DESC, nome
    LIMIT {1} OFFSET {2}
    """;

// Pass the formatted query:
var termo = FormatarTermoPostgres(q);

Alternative: FTS via LINQ with EF.Functions (Npgsql)

The Npgsql provider for PostgreSQL offers native Full-Text Search support directly in LINQ, without needing FromSqlRaw. For simpler queries — without custom ranking or prefixes — this approach is cleaner and keeps strong typing:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
// FTS search via pure LINQ — no raw SQL
// Requires: Npgsql.EntityFrameworkCore.PostgreSQL
var itens = await db.Clientes
    .Where(c => EF.Functions.ToTsVector("portuguese", c.Nome + " " + c.Email + " " + c.Cidade)
                    .Matches(EF.Functions.WebSearchToTsQuery("portuguese", q)))
    .OrderBy(c => c.Nome)
    .Skip(offset)
    .Take(tamanho)
    .AsNoTracking()
    .ToListAsync(ct);

⚠️ Warning: The LINQ approach with EF.Functions works well for simple searches, but does not support prefixes (:* / search while typing) and offers less control over ranking. For reactive search with autocomplete — like the scenario in this article — FromSqlRaw with to_tsquery and :* remains the best choice. To understand how EF Core abstracts data access and when to use LINQ vs raw SQL, see EF Core 8 Fluent API: Mapping, ORM and Decoupling.


Oracle: Full-Text Search with Oracle Text (CTX)

How Oracle Text works

Oracle uses Oracle Text (also called Oracle Context or CTX) for Full-Text Search. The indexes are created with CTXSYS.CONTEXT and searches use the CONTAINS() predicate, which returns a relevance score:

  • CONTAINS(coluna, 'João') — simple search
  • CONTAINS(coluna, 'João%') — prefix (right wildcard)
  • CONTAINS(coluna, 'João AND Silva') — AND operator
  • CONTAINS(coluna, 'FUZZY(Joao, 60, 6, N)') — fuzzy search (tolerates spelling errors)

Prerequisite: enabling Oracle Text

Oracle Text (formerly called Oracle ConText Cartridge or InterMedia Text) is included in all Oracle Database editions — Standard Edition 2, Enterprise Edition and Oracle Database Free (XE). It must be installed in the CTXSYS schema and the application user must have the correct privileges.

Check whether the component is installed:

1
2
3
4
5
-- Requires access to DBA_REGISTRY (DBA or SELECT_CATALOG_ROLE)
SELECT comp_name, version, status
FROM   dba_registry
WHERE  comp_name = 'Oracle Text';
-- STATUS must be 'VALID'; if missing, the component is not installed

If it is not installed, run as SYS AS SYSDBA:

1
2
3
4
5
6
7
8
9
-- Installs Oracle Text in the database (run only once)
-- ctxsys = user that owns Oracle Text
-- SYSAUX  = default tablespace for the CTXSYS schema
-- TEMP    = temporary tablespace
-- NOLOCK  = does not lock the CTXSYS account after installation
@?/ctx/admin/catctx.sql ctxsys SYSAUX TEMP NOLOCK

-- Then compile invalid objects (if needed)
@?/rdbms/admin/utlrp.sql

Grant the required privileges to the application user:

1
2
3
4
5
6
-- Option 1: CTXAPP role (covers CREATE INDEX, CTX_DDL, CTX_QUERY)
GRANT CTXAPP TO meu_usuario;

-- Option 2: granular privileges (production with least privilege principle)
GRANT EXECUTE ON CTXSYS.CTX_DDL   TO meu_usuario;
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO meu_usuario;

Check that the Oracle Text indexing service is active:

1
2
-- The extproc / extjob process is required for the index synchronizer
SELECT program, status FROM v$process WHERE program LIKE '%extproc%';

Complete reference: Oracle Text Application Developer’s Guide · Oracle Text Reference

CTX index setup

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 1. Create language preferences (once)
BEGIN
  CTX_DDL.CREATE_PREFERENCE('pref_clientes', 'BASIC_LEXER');
  CTX_DDL.SET_ATTRIBUTE('pref_clientes', 'BASE_LETTER', 'YES');  -- ignores accents
  CTX_DDL.SET_ATTRIBUTE('pref_clientes', 'MIXED_CASE', 'NO');    -- case insensitive

  CTX_DDL.CREATE_PREFERENCE('wordlist_clientes', 'BASIC_WORDLIST');
  CTX_DDL.SET_ATTRIBUTE('wordlist_clientes', 'PREFIX_INDEX', 'YES');  -- prefix search
  CTX_DDL.SET_ATTRIBUTE('wordlist_clientes', 'PREFIX_MIN_LENGTH', '2');
  CTX_DDL.SET_ATTRIBUTE('wordlist_clientes', 'PREFIX_MAX_LENGTH', '10');
END;
/

-- 2. Create a CONTEXT index on the Nome column
--    For multiple columns, create a computed column or use MULTI_COLUMN_DATASTORE
CREATE INDEX idx_clientes_fts ON clientes(nome)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('LEXER pref_clientes WORDLIST wordlist_clientes');

-- 3. Synchronize the index after inserts/updates (can be scheduled)
EXEC CTX_DDL.SYNC_INDEX('idx_clientes_fts');

-- For auto-synchronization (Oracle 12c+):
-- PARAMETERS ('... SYNC (ON COMMIT)')

Repository — Oracle

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
// ClienteRepositorioOracle.cs
public class ClienteRepositorioOracle(AppDbContext db) : IClienteRepository
{
    public async Task<(IReadOnlyList<Cliente> itens, int total)> BuscarAsync(
        string? q, int pagina, int tamanho, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(q))
            return await BuscarSemFiltroAsync(pagina, tamanho, ct);

        var termo = FormatarTermoOracle(q);
        var offset = (pagina - 1) * tamanho;

        // CONTAINS returns a 0-100 score; > 0 means it found a match
        // SCORE(1) is the score of the first CONTAINS call in the query
        var sql = $"""
            SELECT Id, Nome, CpfCnpj, Email, Cidade
            FROM (
                SELECT c.Id, c.Nome, c.CpfCnpj, c.Email, c.Cidade,
                       SCORE(1) AS relevancia,
                       ROW_NUMBER() OVER (ORDER BY SCORE(1) DESC, c.Nome) AS rn
                FROM Clientes c
                WHERE CONTAINS(c.Nome, :termo, 1) > 0
            )
            WHERE rn > :offset AND rn <= :limite
            ORDER BY rn
            """;

        var countSql = """
            SELECT COUNT(*)
            FROM Clientes
            WHERE CONTAINS(Nome, :termo, 1) > 0
            """;

        var itens = await db.Clientes
            .FromSqlRaw(sql, new OracleParameter("termo", termo),
                             new OracleParameter("offset", offset),
                             new OracleParameter("limite", offset + tamanho))
            .AsNoTracking()
            .ToListAsync(ct);

        var total = await db.Database
            .SqlQueryRaw<int>(countSql, new OracleParameter("termo", termo))
            .FirstAsync(ct);

        return (itens, total);
    }

    private static string FormatarTermoOracle(string q)
    {
        // "João Si" → "João% AND Si%"
        // The % in Oracle Text is a prefix wildcard (similar to LIKE 'Si%')
        var palavras = q.Trim()
            .Split(' ', StringSplitOptions.RemoveEmptyEntries)
            .Select(p => $"{EscaparOracle(p)}%");

        return string.Join(" AND ", palavras);
    }

    private static string EscaparOracle(string p)
        => p.Replace("'", "''").Replace("%", "\\%").Replace("_", "\\_");

    private async Task<(IReadOnlyList<Cliente>, int)> BuscarSemFiltroAsync(
        int pagina, int tamanho, CancellationToken ct)
    {
        var offset = (pagina - 1) * tamanho;
        var itens = await db.Clientes
            .AsNoTracking()
            .OrderBy(c => c.Nome)
            .Skip(offset)
            .Take(tamanho)
            .ToListAsync(ct);

        var total = await db.Clientes.CountAsync(ct);
        return (itens, total);
    }
}

Registering the repositories per database

In Program.cs, select the repository according to the configured database:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
// Program.cs
var banco = builder.Configuration["DatabaseProvider"]; // "sqlserver" | "postgres" | "oracle"

builder.Services.AddScoped<IClienteService, ClienteService>();

builder.Services.AddScoped<IClienteRepository>(sp =>
{
    var db = sp.GetRequiredService<AppDbContext>();
    return banco switch
    {
        "postgres" => new ClienteRepositorioPostgres(db),
        "oracle"   => new ClienteRepositorioOracle(db),
        _          => new ClienteRepositorioSqlServer(db)   // default: SQL Server
    };
});

// DbContext — selects the provider
builder.Services.AddDbContext<AppDbContext>(opt =>
{
    if (banco == "postgres")
        opt.UseNpgsql(builder.Configuration.GetConnectionString("Postgres"));
    else if (banco == "oracle")
        opt.UseOracle(builder.Configuration.GetConnectionString("Oracle"));
    else
        opt.UseSqlServer(builder.Configuration.GetConnectionString("SqlServer"));
});

Comparison of the three databases

FeatureSQL ServerPostgreSQLOracle
MechanismFull-Text Catalog + FT Indextsvector + GIN IndexOracle Text (CTX)
Search predicateCONTAINS(), FREETEXT()@@ (tsvector/tsquery operator)CONTAINS()
Prefix search"termo*"'termo':*termo%
Native rankingCONTAINSTABLE RANKts_rank()SCORE()
StemmingPer language (LANGUAGE)Per language (dictionary)Per lexer
AccentsDepends on collationunaccent extensionBASE_LETTER = YES
Index updateAutomatic (background)Trigger on the columnManual/SYNC or ON COMMIT
SetupCREATE FULLTEXT INDEXCREATE INDEX ... USING GINCTX_DDL.CREATE_PREFERENCE + CREATE INDEX INDEXTYPE CTXSYS.CONTEXT
Required licenseIncluded in SQL ServerNative, no extra costOracle Text (included)

Good Practices and Precautions

1. Protect against injection in the FTS predicate

The CONTAINS and to_tsquery predicates are not immune to injection — they have their own syntax that can be manipulated. Always:

  • Use parameters ({0}, :param) when the provider supports them
  • Escape special characters before interpolating strings
  • Limit the maximum length of the search term
1
2
3
4
5
6
// Validation in the controller — rejects terms that are too long or contain control characters
if (q is { Length: > 100 })
    return BadRequest("Search term too long.");

if (q?.Any(c => char.IsControl(c)) == true)
    return BadRequest("Invalid search term.");

2. Index latency in Oracle

Oracle Text does not index new records automatically by default. Configure synchronization:

1
2
3
4
5
6
7
-- Optional in CREATE INDEX: updates the index on every commit
CREATE INDEX idx_clientes_fts ON clientes(nome)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('... SYNC (ON COMMIT)');

-- Or schedule via DBMS_JOB / DBMS_SCHEDULER for periodic sync:
EXEC CTX_DDL.SYNC_INDEX('idx_clientes_fts', '2M');  -- 2-minute memory window

3. Stopwords and short terms

FTS ignores stopwords (“de”, “do”, “da”, “e”, “o”, “a”…). A search for a single stopword returns zero results, which can confuse the user. Handle it:

1
2
3
4
// In the service: if the result is zero and the term has only stopwords,
// return a specific message or fall back to LIKE
if (total == 0 && TermoContemApenasStopwords(q))
    return await ListarSemFiltroAsync(pagina, tamanho, ct);

4. Caching frequent results

Searches with popular terms (“José”, “Maria”) can be cached in Redis with a short TTL:

1
2
3
4
5
6
7
var cacheKey = $"clientes:fts:{q}:p{pagina}:t{tamanho}";
var cached = await cache.GetAsync<PagedResult<ClienteDto>>(cacheKey);
if (cached is not null) return cached;

var resultado = await repo.BuscarAsync(q, pagina, tamanho, ct);
await cache.SetAsync(cacheKey, resultado, TimeSpan.FromSeconds(30));
return resultado;

5. Minimum characters — in the API too

Even with the filter in Angular, the API must validate:

1
2
if (q is { Length: > 0 and < 3 })
    return BadRequest("The search term must have at least 3 characters.");

Conclusion

Full-Text Search is not a niche feature — it is the right path for any text search on tables with relevant volume. The difference between a LIKE '%termo%' and a CONTAINS() or @@ can be the difference between an 8-second query and a 40-millisecond one.

The complete flow I covered:

  1. Angular with debounceTime and distinctUntilChanged — avoids a flood of requests
  2. Minimum of 3 characters — both in the frontend and in the API
  3. ASP.NET Core with CancellationToken — cancels queries if the client disconnects
  4. Full-Text Index in the database — inverted index, not a full scan
  5. Prefix search ("termo*" / 'termo':* / termo%) — natural “search while typing” behavior
  6. Pagination — because even FTS with thousands of results needs pagination

The pattern repeats regardless of the database: create the database-specific inverted index, use the correct predicate and pass the formatted term. EF Core with FromSqlRaw is the integration point — flexible enough to use each database’s native feature without giving up C#’s strong typing.


Read Also


References

SQL Server

PostgreSQL

Oracle

EF Core and Angular