sql query order output

I have table called Property and the data is like

   pid     city           state          state_abb      address1   address2
    x1     NewCity        NHANy              NH          xxxx      gfg
    x2     Gloucester     Manchestar         MA          newAde    xxxx
    x3     OtherC         NewYork            NY          yyyy

I want a query with search keywo开发者_JS百科rd to display order like (state or state_abb), city, address1, adress2

For ex:

If I search keyword with New The result output should

    pid   city         state        state_abb   address1    address2
     x3   OtherC       NewYork         NY       yyyy 
     x1   NewCity      NHANy           NH       xxxx        gfg
     x2   Gloucester   Manchester      MA       newAde      xxxx

I don't want unmatched rows.I want display only matched rows.

Thanks in advance

It think the question is how to order the results, not how to perform the search. Try this as your ORDER BY clause

order by
 case when charindex('NEW',state) >0 then 1000 else 0 end desc,
 case when charindex('NEW',city) >0 then 100 else 0 end desc, 
 case when charindex('NEW',address) >0 then 10 else 0 end desc

Where clause to only get matching rows

 select * from Property where
        city     like '%New%' or
        state    like '%New%' or
        address1 like '%New%' or
        address2 like '%New%'

You could try something like this:

SELECT pid, city, state, stat_abb, address1, address2
from property
where city like '%New%'
OR state like '%New%'
OR address1 like '%New%'

This should get you started:

select * from Property where
    city     like '%New%' or
    state    like '%New%' or
    address1 like '%New%' or
    address2 like '%New%'

I assume you don't want to look at the pid column.

select *
from Property 
order by case
           when state     like 'New%' then 1
           when state_abb like 'New%' then 1
           when city      like 'New%' then 2
           when address1  like 'New%' then 3
           when address2  like 'New%' then 4
         end