Bit confused on how to get it work better than what has been done now. I am new, apologies if I'm asking for too much.
I have trip details which have from and to locations stored in database from Google MAPS API.
One such record could be like this :
From : Bangalore, Karnataka, India To : New Jersey, United States
If someone search for
From : Bangalore, Karnataka, India
From : Karnataka, India
From : Bangalore
From : Karnataka
From : India
From : Bangalore, India
Query should be success and same condition applies to TO column also. And if any one of FROM or TO column match is valid result.
How to achieve above solution with ordering better matches on top?
What I tried
Exact match solution.(Either matching FROM or matching TO and MATCHING BOTH, if given) Not a right solution as it doesn't give all matches.
LIKEbut results are in right order (most or complete matches should be on top is not achieved by me)
CONTAINScannot be done as my SQL Server is on SQL Azure which doesn't support.
Create 3 columns for
FROMand 3 columns for
TOand populate data from
from1,from2,from3and query for
(exact match) UNION this with (query for keyword LIKE in from1,from2,from3 columns)
and do the same for TO column.
None of the solutions as I did now are convincing for me. Any better possible solutions?