Full-text search

Syntax

EXECUTE FREETEXT (

SELECT {* | expression [[AS] alias] [, ...]]}
 FROM index_name
 [WHERE search_expression]
 [RESTRICT offset, limit]
 [ORDER BY expression [ASC | DESC] [, ...]]
)

search_expression syntax

expression {= } {expression | (sub_select)}
expression BETWEEN lower_expression AND upper_expression
expression IN (sub_select)
search_expression {AND | OR} search_expression

Description

EXECUTE FREETEXT (…) code block executes SQL SELECT statement where source is the full-text index referred by index_name.

Search Clauses

The WHERE clause will narrow the results of the selected index to whever the search_expression is true. Only {=} comparison operator is allowed. The text value of the condition may have a string to match or Boolean expression using operators below we illustrate various search terms:

title = ‘hello’	Match all documents containing string “hello” in indexed “title” field
title = ‘hello world’	Match all documents containing “hello” or “world”
title=’hello AND world’	Match all documents containing “hello” and “world”
title = ‘ ”hello world” ’	Match all documents containing “hello world” phrase
title=’hello AND NOT world’	Match all documents containing “hello” but not “word”

For more go to: http://lucene.apache.org/java/docs/queryparsersyntax.html

It is possible to construct Boolean queries with “OR”, “AND” SQL operators. The SQL optimizer internally will convert SQL Boolean expression to Lucene’s syntax. For instance:

SQL expression	Converted Lucene expression
(title=’scimore’ or title=’lucene’) and title=’DB’	(scimore or lucene) and DB

The IN search term will return documents that matches at least single string in the IN set. For instance:

SQL expression	Equivalent Lucene expression
title IN(’scimore’,’lucene’)	scimore or lucene

BETWEEN queries searches for the documents in the range between starting term through an ending term. The range query can be applied only on those fields that content has not been tokenized i.e. defined with KEYWORD prefix in the full-text index create command. If field type is number it will be padded with zeros to preserve correct sort order or if DATETIME will be converted to YYYYMMDD:

SQL expression	Equivalent Lucene expression
postID BETWEEN 0 AND 100	postID:[0000000000 TO 0000000100]

Restrict

The RESTRICT offset, limit clause is used to restrict the number of matched documents limit with an offset from the beginning of offset rows.

Ordering

Ordering is allowed on fields that content has not been tokenized.

Total number of matches

Number of matched documents can be obtained using {#TotalCount} field in full-text index:

use debate
execute freetext
(
select postid,[#TotalCount] from debate_search where
pagetext='hello world'
restrict 0,10
order by dateline desc
)