Full-text query

Full-text query refers to the operation of performing a full-text search or retrieval in text data. It allows for a more comprehensive search across the entire text and returns results that match the search criteria.

Syntax

Use the following statement to perform a full-text index query. Specify the full-text index column and find the degree of match with search_query. The return value is a non-negative DOUBLE indicating the relevance between the row and search_query, where 0 indicates no relevance.

CONTAINS(indexed_column, 'search_query' [, score_label])

SCORE(score_label)

Parameter description:

• indexed_column: the name of the column for the full-text index.

• search_query: The keyword or phrase to be searched, of the VARCHAR2 or CLOB type. The following search operators are supported:

  • Space: Indicates a consecutive phrase match, matching adjacent words in order. For example, 'word1 word2'. Escaping with double quotes "" or curly braces {} is supported. For instance, to search for the phrase "law and order", directly writing 'law and order' would be parsed as law AND order (logical AND). Solution:

    • Using curly braces: '{law and order}'.
    • Use double quotes: '"law and order"'.

  Note

  If the query content of the search_query parameter generates too many tokens under the corresponding tokenizer, it may lead to increased system resource consumption, slower response, and even impact query performance. It is recommended to keep queries concise and reasonable for optimal execution efficiency.

• score_label: Optional. The matching number of the returned result, in NUMBER type.

• SCORE(): Optional. The SCORE() function is used together with score_label. score_label must be the score_label specified for CONTAINS and must be of the NUMBER type. It outputs the matching score of CONTAINS (higher scores indicate a higher degree of relevance).

Limitations and considerations

• An error will be returned if indexed_column does not exist.
• An error is reported when a full-text index is not created for the indexed_column.
• An error will be returned if search_query is NULL. For example, SELECT col1 FROM tbl1 WHERE CONTAINS(col2, NULL, 1) > 0;.
• If search_query is an empty string, an empty result set is returned. For example, SELECT col1 FROM tbl1 WHERE CONTAINS(col2, '', 1) > 0;.
• If search_query consists entirely of spaces, an empty string is returned. For example, SELECT col1 FROM tbl1 WHERE CONTAINS(col2, ' ', 1) > 0;.
• An error will be returned if score_label is negative. For example, SELECT col1 FROM tbl1 WHERE CONTAINS(col2, 'OceanBase', -1) > 0;.
• An error will be returned if the SCORE tag is not defined. For example, SELECT col1 FROM tbl1 WHERE CONTAINS(col2, 'OceanBase', 1) > 0 ORDER BY SCORE(999);.
• An error will be returned if SCORE is used without the CONTAINS context. For example, SELECT SCORE(1) FROM tbl1;.
• The SCORE expression is not supported in the GROUP BY or HAVING clauses.
• An error is returned when CONTAINS is not compared. For example, SELECT col1 FROM tbl1 WHERE CONTAINS(col2, 'OceanBase', 1);.
• An error will be returned when CONTAINS uses a less than, equal to, or less than or equal to comparison. For example, SELECT col1 FROM tbl1 WHERE CONTAINS(col2, 'OceanBase', 1) <= 0;.
• Fixed an error when performing a table join or table rename query with a full-text filter. For example, SELECT SCORE(1) FROM tbl2 t1 WHERE CONTAINS(t1.col2, 'OceanBase', 1) >0;.

Examples

1. Create table tbl1.

   obclient> CREATE TABLE tbl1(
       col1 INT,
       col2 CHAR(50),
       col3 VARCHAR2(500));
   

2. Create a full-text index named ft_idx1_tbl1.

   obclient> CREATE INDEX ft_idx1_tbl1 ON tbl1(col2)
       INDEXTYPE IS CTXSYS.CONTEXT
       PARAMETERS('LEXER SPACE')
       LOCAL;
   

3. Create a full-text index named ft_idx2_tbl1.

   obclient> CREATE INDEX ft_idx2_tbl1 ON tbl1(col3)
       INDEXTYPE IS CTXSYS.CONTEXT
       PARAMETERS('LEXER SPACE')
       LOCAL;
   

4. Add test data to the tbl1 table.

   obclient> INSERT INTO tbl1 (col1, col2, col3) VALUES 
       (1, 'Hello World', 'This is a test'),
       (2, 'OceanBase', 'OceanBase Database is a native, enterprise-level distributed database developed independently by the OceanBase team'),
       (3, 'Database Management', 'Learn about SQL and database administration'),
       (4, 'Full Text Searching', 'Master the art of full text searching');
   

   The return result is as follows:

   Query OK, 4 rows affected
   Records: 4  Duplicates: 0  Warnings: 0
   

5. Use the CONTAINS query.

   obclient> SELECT *
       FROM tbl1
       WHERE CONTAINS(col2, 'OceanBase', 1) > 0;
   

   The return result is as follows:

   +------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+
   | COL1 | COL2                                               | COL3                                                                                                                |
   +------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+
   |    2 | OceanBase                                          | OceanBase Database is a native, enterprise-level distributed database developed independently by the OceanBase team |
   +------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

6. Use the CONTAINS query and sort by SCORE.

   obclient> SELECT col1, col2, col3, SCORE(1)
       FROM tbl1
       WHERE CONTAINS(col2, 'Database', 1) > 0
       AND col1 >= 2
       ORDER BY SCORE(1) DESC;
   

   The return result is as follows:

   +------+----------------------------------------------------+---------------------------------------------+------------------------+
   | COL1 | COL2                                               | COL3                                        | SCORE(1)               |
   +------+----------------------------------------------------+---------------------------------------------+------------------------+
   |    3 | Database Management                                | Learn about SQL and database administration | 1.259496819494492E+000 |
   +------+----------------------------------------------------+---------------------------------------------+------------------------+
   1 row in set
   

7. Multiple queries, and use score_label combined with SCORE to output the results.

   obclient> SELECT col1, col2, col3, SCORE(10), SCORE(20) 
       FROM tbl1
       WHERE CONTAINS (col2, 'Database', 10) > 0
       OR CONTAINS (col3, 'OceanBase', 20) > 0 
       ORDER BY SCORE(10), SCORE(20);
   

   The return result is as follows:

   +------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+------------------------+-------------------------+
   | COL1 | COL2                                               | COL3                                                                                                                | SCORE(10)              | SCORE(20)               |
   +------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+------------------------+-------------------------+
   |    2 | OceanBase                                          | OceanBase Database is a native, enterprise-level distributed database developed independently by the OceanBase team |                      0 | 1.1331643117640413E+000 |
   |    3 | Database Management                                | Learn about SQL and database administration                                                                         | 1.259496819494492E+000 |                       0 |
   +------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+------------------------+-------------------------+
   2 rows in set
   

References

• Create an index
• Full-text index