QUERY_EXPERT_SEARCH Function

This function converts an end-user search query into the corresponding Oracle Text syntax, enabling advanced and precise searching capabilities.

It processes the search expression and generates a custom search query that can be used with Oracle Text for efficient and accurate text-based searches.

About Search Expression Syntax

The search expression provided as the parameter follows a specific syntax and can include the following elements:

• Operators - The search expression can contain specific operators that modify the search behavior:

  • AND - The AND operator is used for combining multiple search terms. For example, “red AND blue” retrieves documents containing both red and blue.

  • OR - The OR operator is mapped to the ACCUM operator of Oracle Text and is used for combining search terms and retrieving documents containing any of the terms. For example, “red OR blue” retrieves documents containing either red or blue with a higher score for the document matching both terms.

  • NOT - The NOT operator is used to exclude specific terms from the search results. For example, “red NOT blue” retrieves documents containing red but not blue.

  • AROUND(d) - The AROUND operator is an abstraction of the NEAR operator in Oracle Text. It is used to find terms within a certain distance (d) of each other. The distance parameter (d) represents the maximum number of words permitted between the two query terms. For example, “red AROUND(3) blue” retrieves documents where red and blue appear within three words or less of each other with a higher score if both terms are closer together.

• Parentheses - Parentheses can be used to group search terms and specify the order of evaluation. For example, “(red OR blue) AND green” retrieves documents containing either red or blue and green.

• Quoted Phrases - Quoting a phrase (such as “red apple”) ensures that the exact phrase is searched for as a whole. For example, “red apple” retrieves documents containing the exact phrase red apple.

• Fuzzy Prefix - The syntax enables fuzzy matching using the FUZZY keyword followed by an optional plus (+) or minus (-) sign to increase or decrease the fuzziness. For example, “fuzzy+: red apple” performs a fuzzy match with increased fuzziness for red and apple.

• Weighted Query Terms - The search expression supports weighting search terms using the caret symbol (^) followed by a numeric value to indicate the importance or weight of a term. For example, “red^3 apple” assigns a higher weight to the term red compared to apple.

Syntax

APEX_SEARCH.QUERY_EXPERT_SEARCH (
    p_search_expression IN VARCHAR2 )
RETURN CLOB;

Parameters

Parameter	Description
p_search_expression	End-user search query to convert to Oracle Text syntax. It can include various search operators and keywords.

Returns

This function returns the generated Oracle Text query based on the provided search expression.

Example 1

select query_expert_search('(red or white) "summer shorts"') from dual;

TEXT_QUERY
----------------------------------------
({red} ACCUM {white}) AND {summer shorts}

Example 2

select query_expert_search('fuzzy-: catz dogz') from dual;

TEXT_QUERY
----------------------------------------
FUZZY({catz},80,100,W) AND FUZZY({dogz},80,100,W)

Example 3

select query_expert_search('oracle^3 apex') from dual;

TEXT_QUERY
----------------------------------------
{oracle}*3 AND {apex}