42 · Full-Text Search

Enable fuzzy full-text search with @hana.fullTextIndex, use OData $search, and tune FUZZY() thresholds.

Episode 42 — Full-Text Search in HANA

What you'll learn

• HANA full-text index vs SQL LIKE
• Enabling full-text search with @hana.fullTextIndex
• OData $search and contains() predicates
• Fuzzy search configuration
• Linguistic analysis and language detection

Why this matters

LIKE '%search%' scans every row, every character. HANA full-text search uses an inverted index — the same technique as Elasticsearch — that answers text searches in milliseconds even on 100 million rows. It also handles synonyms, inflections, and typos.

Full-text index annotation

entity Products {
  key ID          : UUID;
  name            : String          @hana.fullTextIndex;
  description     : LargeString     @hana.fullTextIndex: {
    fuzzySearchIndex:  true,         // tolerate typos
    text_analysis:     true,         // language-aware tokenization
    fast:              true          // FAST index (better performance, more space)
  };
  technicalSpecs  : String;         // not indexed — not searched
}

OData $search system query option

Once the full-text index is in place, standard OData $search works:

GET /catalog/Products?$search=machine learning

CAP translates $search into HANA CONTAINS(*) automatically for full-text indexed entities.

OData $filter with contains()

# Exact phrase
GET /catalog/Products?$filter=contains(description,'machine learning')

# Fuzzy search (0.8 = 80% similarity threshold)
GET /catalog/Products?$filter=search.ismatch('machin lernig','description','full','false','0.8')

Direct HANA SQL full-text query

const db = await cds.connect.to('db')

this.on('searchProducts', async (req) => {
  const { query, fuzzy = 0.8 } = req.data

  const results = await db.run(`
    SELECT TOP 20
      ID, name, description,
      SCORE() AS relevance
    FROM PRODUCTS
    WHERE CONTAINS(name, description, ?, FUZZY(${fuzzy}))
    ORDER BY SCORE() DESC
  `, [query])

  return results
})

Fuzzy search parameters

Parameter	Range	Effect
FUZZY(0.9)	0.0–1.0	Higher = stricter match
FUZZY(0.7, 'textSearch=compare')	—	Error-tolerant text matching
FUZZY(0.8, 'similarCalculationMode=compare')	—	Character comparison mode

Language-aware tokenization

entity Articles {
  title   : String @hana.fullTextIndex: { text_analysis: true };
  // text_analysis enables:
  // - stemming (run/running/ran → run)
  // - stop word filtering (the, a, is)
  // - language detection per document
}

-- After text_analysis is enabled
SELECT * FROM ARTICLES
WHERE CONTAINS(title, 'running', LINGUISTIC);
-- Matches: run, running, ran, runner (stemmed matches)

Indexing strategy

Scenario	Recommendation
Short strings (< 100 chars)	@hana.fullTextIndex (basic)
Long text, typo tolerance	@hana.fullTextIndex: { fuzzySearchIndex: true }
Multilingual content	text_analysis: true
Maximum performance	fast: true (uses more disk space)
Structured data (IDs, codes)	Don't use full-text index; use standard index instead

OData search on multiple entities (cross-entity)

GET /catalog/SearchResults?$search=enterprise&$select=type,title,snippet

// Handler aggregates results from multiple entities
this.on('READ', 'SearchResults', async (req) => {
  const { '$search': query } = req._.req.query

  const [products, docs, faq] = await Promise.all([
    db.run(`SELECT ID,'product' AS type,name AS title FROM PRODUCTS WHERE CONTAINS(name,?)`, [query]),
    db.run(`SELECT ID,'doc' AS type,title FROM DOCUMENTS WHERE CONTAINS(title,description,?)`, [query]),
    db.run(`SELECT ID,'faq' AS type,question AS title FROM FAQ WHERE CONTAINS(question,answer,?)`, [query])
  ])

  return [...products, ...docs, ...faq]
    .sort((a, b) => (b.SCORE || 0) - (a.SCORE || 0))
    .slice(0, 20)
})

Testing on SQLite (development)

Full-text index annotations are ignored on SQLite. Test $search on HANA Dev instance:

# Run against HANA (not SQLite)
CDS_ENV=staging cds watch
# Then test: GET http://localhost:4004/catalog/Products?$search=machine

Hands-on exercise

1. Add @hana.fullTextIndex: { fuzzySearchIndex: true } to title and description on Books
2. Deploy to HANA and test: GET /catalog/Books?$search=cloud programming
3. Test fuzzy: search for progaming (typo) — should still return CAP books
4. Add SCORE() to the result by wrapping the OData call with a native SQL handler

Checkpoint ✓

• [ ] @hana.fullTextIndex annotation enables HANA full-text indexing
• [ ] OData $search=term maps to CONTAINS(*) automatically
• [ ] FUZZY(0.8) threshold: 0.0 = anything matches, 1.0 = exact only
• [ ] Full-text annotations are silently ignored on SQLite — test on HANA

Sign in for progress tracking & Q&A →