user3683503 user3683503 - 6 months ago 17
SQL Question

Query to search multiple columns

I have similar table as below:

Name city state country
---- ---- ----- -------
Sree mm ap ind
SRee redmond ny us
rahul hyd ap ind
xxx mm ap ind
abcd mm tn ind
wer dd ap ind


If I search with
mm,ap
I need to get these.

Name city state country
---- ---- ----- -------
Sree mm ap ind
xxx mm ap ind
abcd mm tn ind
wer dd ap ind


If the two words match it should come first if contains in
city
second as state and third as country.

Please help me with this.

Answer
declare @city nvarchar(50) = 'mm',
        @state nvarchar(50) = 'ap'

Select *
From (Select *
    from YourTable
    Where city = @city or state = @state
    )z
order by Case When city = @city and state = @state Then 1
              When City = @city then 2
              Else 3
          end