Weighting the results of a text search
I have an application that is using Oracle Text to search the database for similar customer names. What I am trying to do is give the developers a result-set with the most relavent matches listed first, but I cannot figure out how to score/weight the results.
Example:
Here is my query:
select * from operator z
where contains
(concat,'hilton hotel within {the_operator_name}',1 ) > 0
It returns the following records in order:
OPERATOR_ID OPERATOR_NAME
----------- ----------------------------------
1000084 Hilton Hotel
1001397 HILTON HOTEL AIRPORT
1001472 Hilton Hotel & Conference Ctr.
1004356 Hilton Hotel/Avendra Eugene
1005660 Hilton Hotel
I would like both "Hilton Hotel" results (the first and last in the list) to return before any of the other results, as these are a perfect match and are most likely to be picked by the user.
Thanks in advance.