Query to match exact column or partial data of the column: SQL Server / Azure
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
        &lt;table PK&gt; -- Unique match guaranteed
        , 1 as [present] -- Convenient summing later via the isnull function
    from &lt;tablename&gt;
    where
        [city] = @city
)
,  [Region_CTE] AS
(
    select
        &lt;table PK&gt;
        , 1 as [present]
    from &lt;tablename&gt;
    where
        [region] = @region
)
, [Country_CTE] AS
(
    select
        &lt;table PK&gt;
        , 1 as [present]
    from &lt;tablename&gt;
    where
        [Country] = @country
)
select
    *  -- Select the columns you need
    , isnull([ci].[present], 0) + isnull([re].[present], 0) + isnull([co].[present], 0) as [MatchWeight]
from &lt;tablename&gt; [t]
    left join [City_CTE] [ci] on [ci].&lt;table PK&gt; = [t].&lt;table PK&gt;
    left join [Region_CTE] [re] on [re].&lt;table PK&gt; = [t].&lt;table PK&gt;
    left join [Country_CTE] [co] on [co].&lt;table PK&gt; = [t].&lt;table PK&gt;
order by 
    [MatchWeight] desc