Basic Text Search query syntax

Use the bts_contains() search predicate to run basic text search queries.

You can also run a basic text search query with the HCL OneDB™ JSON $ifxtext query operator. Use the same syntax for the search criteria for both methods.

bts_contains() Search Predicate

1  bts_contains ( column , ' %Search criteria '?  , score # REAL  )
Search criteria

1 ?  %Index field :(1) query_string
column
The column to be searched. It must be a single column for which a bts index is defined.
query_string
The search string. The search string includes the following elements:
Query term
Required. One or more words that you want to search for.
Query term modifiers
Optional. You can modify query terms to run wildcard, fuzzy, proximity, and range searches. You can boost the importance of a query term relative to other terms.
Boolean operators
Optional. You can include Boolean operators to combine query terms in logical combinations.
If an index has multiple fields because it is a structured or a composite index, you can include an index field name to modify the search string.
score # REAL
Optional argument that is used to pass a statement local variable (SLV) to the text search engine. The search engine uses this variable to record the document score it assigns to each row in the results. The score value is a REAL number between 0.0 and 100.0 inclusive that indicates the relevance of each document to the search criteria, compared to that of other indexed records. The higher the document score value, the more closely the document matches the criteria.
The following example shows a search for the word standard in the column brands in a table called products.
SELECT id FROM products 
WHERE bts_contains(brands, 'standard');

You can use an SLV as a filtering mechanism and to sort the results by score. The following example returns documents that contain the word standard from the column brands in a table that is called products if the document score value is greater than 70. The results are ordered in descending order by score.

SELECT id FROM products 
WHERE bts_contains(brands, 'standard', score # REAL)
AND score > 70.0;
ORDER BY score DESC;