HIGHLIGHT

Syntax

HIGHLIGHT (expression) AGAINST (query_expression, [max_number_fragments])

Arguments

expression: Any set of expressions. This may be a column name, the result of another function, or a math operation.
query_expression: The supported possible expression values are the same as the MATCH syntax.

Note: Fuzzy and wildcard search are not supported in HIGHLIGHT.
max_number_fragments: The maximum number of elements to return in the fragments JSON array.

Note: highlight_fragment_size is a global variable that sets the size of the fragment for all highlight queries.

Remarks

Unlike MATCH, HIGHLIGHT does not require a FULLTEXT index. This allows HIGHLIGHT to run on an arbitrary string expression, but at the expense of a longer query time compared to MATCH.
Use HIGHLIGHT in the SELECT list to return fragments from the matching text. Use MATCH in the WHERE clause to select rows that match the search expression.
The HIGHLIGHT function can be used in the following ways:
- Without a FULLTEXT index on an arbitrary string expression.
- With MATCH and a Version 1 FULLTEXT index.
- With the Version 2 full-text search functions and a Version 2 FULLTEXT index. Version 2 functions include MATCH, BM25, and BM25_GLOBAL.

SingleStore recommends Version 2 for new development because Version 1 is deprecated.

Output

Each string value (the raw string stored in the column that is indexed) conceptually can be thought of as fragments of approximately the same size (default is 100 characters and fragments are aligned to a token boundary). There is no physical change to how the raw string is stored. HIGHLIGHT returns the fragments that contain the match. Each fragment is accompanied by the offset into the document at the location of the fragment, and the number of matches within the fragment.

The result is a JSON blob. It contains one array named fragments. The element represents a fragment that has at least one match. Each element of fragments is an object with three members as described below:

Name	Type	Description
offset	number	The number of characters from the beginning of the text to the beginning of the fragment.
unique_term_count	number	The number of unique matches found within the fragment.
text	string	A snippet of text surrounding the matched text. The matched text is bolded using HTML tags.

The result will be NULL if no term is found.

Examples

Highlight Matching Text

The following example extracts the relevant part of a field.

SQL

SELECT HIGHLIGHT (body) AGAINST ('mother') FROM books;

{"fragments":[{"offset":408,"unique_term_count":1.000000,"text":"
oblique line in her magnolia-white skin--that skin so prized by
Southern women and <B>mother</B> and so"},{"offset":0,
"unique_term_count":1.000000,"text":"In her face were too sharply
blended the delicate features of her <B>mother</B>, a Coast aristocrat of French"}]}

Use HIGHLIGHT on string literals to extract specific terms.

SQL

SELECT HIGHLIGHT(
    "Each string value (the raw string stored in the column that is indexed)
    conceptually can be thought of as fragments of approximately the same size
    (default is 100 characters and fragments are aligned to a token boundary).
    There is no physical change to how the raw string is stored.
    `HIGHLIGHT` returns the fragments that contain the match. Each fragment is
    accompanied by the offset into the document at the location of the fragment,
    and the number of matches within the fragment.") against("fragment");

{"fragments":[{"offset":311,"text":" the match. Each <B>fragment</B>
is accompanied by the offset into the document to the location of the
 <B>fragment</B>","unique_terms":1},{"offset":415,"text":", and the
 number of matches within the <B>fragment</B>","unique_terms":1}]}

It can also be used in combination with MATCH to retrieve relevant parts from relevant rows. This is the recommended way of using HIGHLIGHT.

SQL

SELECT name, HIGHLIGHT(doc) AGAINST ("fragment") FROM articles WHERE MATCH(doc) AGAINST ("+memsql + highlight");

{"fragments":[{"offset":202,"text":
" to a token boundary). There is no physical change to how it is stored.
 `HIGHLIGHT` returns the fragments that contain the match.
 Each <B>fragment</B> is accompanied by the offset into the document to
the location","unique_terms":1},{"offset":408,"text":" you yi zhi tu tu shi
 xiao ben ben. the <B>fragment</B>, and the number of matches within the
 <B>fragment</B>. you yi zhi tu tu shi xiao ben ben. wo xi huan xiao ben
 ben tu tu","unique_terms":1}]}

Highlight Text from a Version 2 Index

The following example creates a Version 2 FULLTEXT index, uses MATCH to select rows containing database in the body column, and uses HIGHLIGHT to return up to two matching fragments from each selected body value.

SQL

CREATE TABLE articles (
    id INT,
    title VARCHAR(200),
    body TEXT,
    SORT KEY (id),
    FULLTEXT USING VERSION 2 art_ft_index (title, body)
);

INSERT INTO articles (id, title, body) VALUES
    (1, 'Introduction to SQL',
     'SQL is a standard language for accessing and manipulating databases.'),
    (2, 'Database Optimization',
     'Learn about techniques to improve database performance.');

-- Make recent inserts available to the full-text index before searching.
OPTIMIZE TABLE articles FLUSH;

SELECT
    id,
    title,
    HIGHLIGHT(body) AGAINST ('database', 2) AS highlights
FROM articles
WHERE MATCH (TABLE articles) AGAINST ('body:database');

Note: For an existing Version 1 FULLTEXT index, the HIGHLIGHT expression remains unchanged, but MATCH uses the Version 1 syntax.

MATCH

On this page

Syntax

Arguments

Remarks

Output

Examples

Highlight Matching Text

Highlight Text from a Version 2 Index

Was this article helpful?

On this page

Was this article helpful?

HIGHLIGHT

On this page

Syntax

Arguments

Remarks

Output

Examples

Highlight Matching Text

Highlight Text from a Version 2 Index

Related Topics

Was this article helpful?

On this page

Was this article helpful?