CONTAINS SQL Example

In the SELECT statement, specify the query in the WHERE clause with the CONTAINS operator. Also specify the SCORE operator to return the score of each hit in the hitlist. The following example shows how to enter a query:

SELECT SCORE(1), title from news WHERE CONTAINS(text, 'oracle', 1) > 0;

You can order the results from the highest scoring documents to the lowest scoring documents using the ORDER BY clause as follows:

SELECT SCORE(1), title from news 
           WHERE CONTAINS(text, 'oracle', 1) > 0
           ORDER BY SCORE(1) DESC;

The CONTAINS operator must always be followed by the > 0 syntax, which specifies that the score value returned by the CONTAINS operator must be greater than zero for the row to be returned.

When the SCORE operator is called in the SELECT statement, the CONTAINS operator must reference the score label value in the third parameter as in the previous example.