Query to match exact column or partial data of the column: SQL Server / Azure

Question

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

1. 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.

2. Using LIKE but results are in right order (most or complete matches should be on top is not achieved by me)

3. FREETEXT and CONTAINS cannot be done as my SQL Server is on SQL Azure which doesn't support.

4. Create 3 columns for FROM and 3 columns for TO and populate data from FROM to from1,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?

Answer 1

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.

• Options 1 and 3 clearly can't work for the reasons you mentioned.
• Option 2 doesn't work either unless you want a search for India to return results for Indiana too.
• Option 4 shows the most promise assuming from1, from2, from3 -> city, region, country, and your input search terms follow the same pattern (city, region, country). If they must be a random list search terms then you may run into problems assigning weights for locations like Lima (city), Lima (region/province), Peru (country) because Lima matches two categories.

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.

declare @city nvarchar(50) = N'Bangalore', @region nvarchar(50) = N'Karnataka', @country nvarchar(50) = N'India';

;with [City_CTE] AS
(
    select
        <table PK> -- Unique match guaranteed
        , 1 as [present] -- Convenient summing later via the isnull function
    from <tablename>
    where
        [city] = @city
)
,  [Region_CTE] AS
(
    select
        <table PK>
        , 1 as [present]
    from <tablename>
    where
        [region] = @region
)
, [Country_CTE] AS
(
    select
        <table PK>
        , 1 as [present]
    from <tablename>
    where
        [Country] = @country
)
select
    *  -- Select the columns you need
    , isnull([ci].[present], 0) + isnull([re].[present], 0) + isnull([co].[present], 0) as [MatchWeight]
from <tablename> [t]
    left join [City_CTE] [ci] on [ci].<table PK> = [t].<table PK>
    left join [Region_CTE] [re] on [re].<table PK> = [t].<table PK>
    left join [Country_CTE] [co] on [co].<table PK> = [t].<table PK>
order by 
    [MatchWeight] desc