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