MATCH

Operators

The AGAINST expression consists of a mix of text with zero or more of the following operators.

Stopwords

Certain words are ignored by full-text search due to their commonality resulting in less relevant results. These are called stopwords. SingleStore’s default list of stopwords is as follows:

a, an, and, are, as, at, be, but, by, for, if, in, into, is, it, no, not, 
of, on, or, such, that, the, their, then, there, these, they, this, to, was, will, with

Wildcard Support

Single and multiple character wildcard searches within single terms are supported (not within phrase queries).

To perform a single character wildcard search use the ? symbol and to perform a multiple character wildcard search use the * symbol.

The single character wildcard search looks for terms that match that with the single character replaced. For example, to search for text or test you can use the search: te?t

Multiple character wildcard searches looks for zero or more characters. For example, to search for test, tests or tester, you can use the search: test* You can also use the wildcard searches in the middle of a term. te*t

Special Characters

Depending on the use case, FULLTEXT matching may not always be compatible with match expressions involving special characters. This is because the default tokenizer for SingleStore’s FULLTEXT removes special characters from the search. The current list special characters are:

+ - && || ! ( ) { } [ ] ^ " ~ * ? : \

Relevancy Score

The relevancy score of an expression in a MATCH statement denotes the ranking of the expression based on the following factors:

• Number of times an expression appears in a column. More occurrences of an expression in the matched column(s) increases its relevancy score.

• Rarity of the expression. Rare words have a higher relevancy score than commonly used words.

• The length of the column containing the expression. A column with a short expression has a higher relevancy score than a column with a long expression.

Full-Text Search MATCH with a SQL Predicate

CREATE TABLE books (
    id INT UNSIGNED,
    name VARCHAR(100),
    publish_year INT UNSIGNED,
    body TEXT,
    SORT KEY (id),  
    FULLTEXT (body));

SELECT count(*)
    FROM books
    WHERE publish_year = 2017 AND MATCH (body) AGAINST ('memsql');

Full-Text Search MATCH for the Word database across Two Columns

CREATE TABLE articles (
    id INT UNSIGNED,
    year int UNSIGNED,
    title VARCHAR(200),
    body TEXT,
    SORT KEY (id),
    FULLTEXT (title, body));

SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database');

Full-Text Search MATCH Using a Single Operator

SELECT title
    FROM articles  
    WHERE MATCH (title) AGAINST ('Database OR "Business Intelligence"');

Full-Text Search MATCH Using Multiple Operators

The following example returns the title of an article that contains either database or Business Intelligence and the string real-time analytics in the body. The special character in real-time is not escaped in order to include results for both real time and real-time.

SELECT title
    FROM articles  
    WHERE MATCH (title) AGAINST ('Database OR "Business Intelligence"')
    AND MATCH(body) AGAINST ("real-time analytics");

Full-Text Search MATCH for Any id with a Specific Naming Convention

This example shows a search for any id with the naming convention id-articleX, where X is the ID number. This search will return id-article1 or id-article227, but it will not return id-227. The special character is escaped here to avoid results including a space, since this violates the convention for assigning ids to each article. Note: This example requires a full-text index on the id column rather than title, as indicated in the CREATE TABLE statement above.

SELECT title
    FROM articles  
    WHERE MATCH(article_id) AGAINST ('"id\\-" +article*');

Full-Text Search MATCH Using a Wildcard

This example uses the wildcard ’*’ to return all articles with titles starting with the word Journal such as Journalism, Journalist, Journals, and so on.

SELECT *
    FROM articles  
    WHERE MATCH(title) AGAINST ('Journal*');

Full-Text Search MATCH Using Special Characters

In the following example, the special character - is escaped to include all results starting with Data-, followed by words starting with di. So, the search result includes words like Data-directory, Data-dictionary, and so on.

SELECT * FROM articles WHERE MATCH(title) AGAINST ('+Data\\- +di*');   

Full-Text Search MATCH Using Special Characters and Operators

This example returns the articles where the title starts with Data and also contains the word function. So, the search result includes words like Database function and Database functions.

SELECT title
    FROM articles  
    WHERE MATCH (title) AGAINST ('+Data* AND function?');

Full-Text Search MATCH Creating a Relevance Score as an Output Column

SELECT id, title, MATCH(body) AGAINST ('database') relevance
    FROM articles
    WHERE MATCH(body) AGAINST ('database');

Full-Text Search MATCH with Greater than a Specific Relevance Score

SELECT id, title, MATCH(body) AGAINST ('database')
    FROM articles
    WHERE MATCH(body) AGAINST ('database') > .12;

Full-Text Search MATCH with UPDATE or DELETE Queries

UPDATE articles set name = concat(name,".DATABASE") where MATCH(body) AGAINST ('database');

DELETE from articles where MATCH(body) AGAINST ('database');