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
or
From : Karnataka, India
or
From : Bangalore
or
From : Karnataka
or
From : India
or
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.
-
Using
LIKE
but results are in right order (most or complete matches should be on top is not achieved by me) -
FREETEXT
andCONTAINS
cannot be done as my SQL Server is on SQL Azure which doesn't support. -
Create 3 columns for
FROM
and 3 columns forTO
and populate data fromFROM
tofrom1,from2,from3
and 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?
Best Answer
As @MarkSinkinson suggested you probably want to bite the bullet and move to the geography datatype. Sometimes a little short-term pain will save you a lifetime of misery.
As I suggested option 4 will probably be the most fruitful, given the caveats I listed for option 4 and the recommendation to abandon text matches in favor of the geography type. The following query isn't sexy but it should be close to your original requirements.