Search and display relevance results in Oracle

Question

I am very new to developing Oracle Database queries. I am developing a search function for my project to search for Vaults Postal Code or Name or City , where the user will be provided with "One HTML input box" to enter a search query. There is only a Called Stores table.

Search Results Display Terms

• If a search query matched against ZIP results should be GROUP and SORT BY ZIP DESC
• If a search query matched against NAME results should be a group search and sorted by NAME IN ASC and similar to CITY
• If the search query matches ZIP , NAME and CITY (for everyone), first the results matched with ZIP should be displayed in DESC order , next CITY followed by The NAME

I tried something 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 does not display the results as expected (I mean that it displays the results without an order, and not as the first zip, the next city, followed by the name).

How can I fulfill the request to satisfy the above requirements, do I need to use stored procedures for this? Any suggestions would be highly appreciated.

Answer 1

My answer may not be for your circuit, but I know that this may help.

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

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

I have :searchKey as a bind variable that must be entered by the user (I use SQL Developer in the Oracle database). I have successfully used 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 relevance to suit your priorities.