SCORE
You can use the SCORE function to search a text search index using criteria that you specify in a search argument. The function returns a relevance score that measures how well a document matches the query.
The schema is QSYS2.
- column-name
- Specifies a qualified or unqualified name of a column that has a text search index that is to be searched. The column must exist in the table or view that is identified in the FROM clause of the statement. The column of the table, or the column of the underlying base table of the view, must have an associated text search index (SQLSTATE 38H12). The underlying expression of the column of a view must be a simple column reference to the column of an underlying table, either directly or through another nested view.
- search-argument
- Specifies an expression that returns a string value containing the terms used in the search. The expression must not be all blanks or the empty string (SQLSTATE 38H14). The actual length of the string must not exceed 32704 bytes. This length might be further limited by what is supported by the text search server (SQLSTATE 38H10). The value is converted to Unicode before it is used to search the text search index. If the search-argument is null, the result is the null value.
- string-constant
- Identifies a string constant that specifies the search argument
options that are in effect for the function.
The options that can be specified as part of the search-argument-options are as follows:
- QUERYLANGUAGE = value
- Specifies the query language. The value can be any of the supported language codes. If the QUERYLANGUAGE option is not specified, the default is the language value of the text search index that is used when this function is invoked. If the language value of the text search index is AUTO, the default value for QUERYLANGUAGE is en_US.
- RESULTLIMIT = value
- Specifies the maximum number of results that are to be returned
from the underlying search engine. The value can
be an integer value 1 - 2 147 483 647. If the RESULTLIMIT option is
not specified, no result limit is in effect for the query.
This scalar function might not be called for each row of the result table, depending on the plan that the optimizer chooses. This function can be called once for the entire query to the underlying search engine. A result set of all the primary keys that match are returned. This result set is then joined to the table containing the column to identify the result rows. In this case, the RESULTLIMIT value acts like FETCH FIRST ?? ROWS from the underlying text search engine and can be used as an optimization. If the search engine is called for each row of the result because that is the best plan, then the RESULTLIMIT option is ignored.
- SYNONYM = OFF or SYNONYM = ON
- Specifies whether to use a synonym dictionary that is associated
with the text search index. You can add a synonym dictionary to a
collection by using the synonym tool.
- OFF
- OFF is the default value.
- ON
- Use the synonym dictionary that is associated with the text search index.
The result of the function is a double-precision floating-point number. If the second argument can be null, the result can be null. If the second argument is null, the result is the null value.
The result is greater than 0 but less than 1 if the column contains a match for the search criteria that the search argument specifies. The more frequently a match is found, the larger the result value. If the column does not contain a match, the result is 0. The score is also 0 if the column is null.
SCORE is a nondeterministic function.
Example
programmer
AND (java OR cobol)
. In addition, a relevance value that
is normalized between 0 (zero) and 100 is returned.SELECT EMPNO, INTEGER(SCORE(RESUME, 'programmer AND
(java OR cobol)') * 100) AS RELEVANCE
FROM EMP_RESUME
WHERE RESUME_FORMAT = 'ascii'
ORDER BY RELEVANCE DESC