Searching and displaying results by relevance in Oracle

Question

I am very beginner to Oracle Database query development. I am developing a search functionality for my project to search for Stores by Zip or Name or City, where the user will be provided with "One HTML input box" to enter the search term. There is only table Called Stores

The conditions for displaying search results are

1. If the search term matched to ZIP results should be GROUP and SORT BY ZIP DESC
2. If the search term matched to NAME results should be group and sort by NAME IN ASC and same as to CITY
3. If the search term matched to ZIP,NAME and CITY (to all), then first the results matched to the ZIP should be displayed in DESC order, next CITY followed by The NAME

I have tried Some thing like this

SELECT s.uuid AS uuid, COUNT(*) over() AS rowcount
FROM Stores s
WHERE s.postalcode LIKE '%87%'
      OR s.city LIKE '%87%'
      OR CONCAT(CONCAT(s.firstname, ' '),s.lastname) LIKE '%87%'            
GROUP BY s.city, s.postalcode, 
      CONCAT(CONCAT(s.firstname, ' '),s.lastname), s.uuid
ORDER BY CASE WHEN s.postalcode LIKE '%87%'
              THEN s.postalcode END DESC,
         CASE WHEN CONCAT(CONCAT(s.firstname, ' '),s.lastname) LIKE '%87%'
              THEN CONCAT(CONCAT(s.firstname, ' '),s.lastname) 
              ELSE s.postalcode END ASC,
         CASE WHEN s.city LIKE '%87%'
              THEN s.city END 

This query is not displaying the results as expected (I mean it is displaying results without order, not Like First zip, next city followed by name).

How can I do the query to meet the above requirements, do I need to use stored procedures to do that ? Any suggestions will be greatly appreciated.

Answer 1

My answer may not be for your schema but I know it can help.

I have a table address that has columns addressid, address1, address2, address3, city, zip, province, countryid, regionid, modified, modifiedby, VERSION, created, createdby.

My requirement is to search by relevance, case insensitive. At the highest priority for relevance is given to the column CITY. Then it is ADDRESS3, ADDRESS2 and ADDRESS1.

I have :searchKey as a bind variable that is to be input by the user (I'm using SQL Developer on Oracle database). I have been successfully using the following:

  SELECT addressid, address1, address2, address3, city, zip
         , province, regionid, countryid
    FROM address
   WHERE UPPER(address1||' '||address2||' '||address3||' '||city)
         LIKE '%' || UPPER(:searchKey) || '%' -- << Makes search case insensitive
ORDER BY CASE
              WHEN UPPER(city) = UPPER(:searchKey)                THEN 10 
              WHEN UPPER(city) LIKE UPPER(:searchKey) || '%'      THEN 9 
              WHEN UPPER(city) LIKE '%'|| UPPER(:searchKey) ||'%' THEN 8
              ELSE 0
          END DESC -- << Highest priority given to cities that match the best
         , CASE WHEN UPPER(address3) LIKE UPPER(:searchKey) ||'%'       THEN 5 
                WHEN UPPER(address3) LIKE '%'|| UPPER(:searchKey) ||'%' THEN 4
                ELSE 0
           END DESC
         , CASE WHEN UPPER(address2) LIKE UPPER(:searchKey) ||'%'       THEN 3
                WHEN UPPER(address2) LIKE '%'|| UPPER(:searchKey) ||'%' THEN 2
                ELSE 0
           END DESC
         , CASE WHEN UPPER(address1) LIKE UPPER(:searchKey) ||'%'       THEN 2 
                WHEN UPPER(address1) LIKE '%'|| UPPER(:searchKey) ||'%' THEN 1
                ELSE 0
           END DESC
;

You can change the values in the CASE construct to allow the relevance as per your priorities.