Using an SQL scalar search function

Figure 3. Using an SQL scalar search function for searching
Using an SQL scalar search function for searching

Net Search Extender offers three scalar text search functions (CONTAINS, NUMBEROFMATCHES, and SCORE) that are seamlessly integrated within SQL. You can use the search functions in the same places that you would use standard SQL expressions within SQL queries. Typical queries are:

SELECT * FROM books WHERE CONTAINS (abstract,'"relational databases"') = 1
          AND PRICE <10

SELECT ISBN, SCORE (abstract, '"relational databases"') as SCORE 
          from BOOKS
          where NUMBEROFMATCHES (abstract, '"relational databases"') 
          >5 AND PRICE <10 
          order by SCORE

The SQL scalar functions return an indicator to how well the text documents matched a given text search condition. Then the SELECT phase of the SQL query determines the information returned to the end user.

Use the SQL scalar search functions as the default search method. These search functions should be suitable for a majority of situations, especially when the text search expression is combined with other, different conditions.

Note that the DB2 Optimizer is aware of how many text documents can be expected to match a CONTAINS predicate and how costly different access plan alternatives will be. The optimizer will choose the cheapest access plan.