Single query for searching for records without knowing what the search column is for
If you want to know which column matched the parameter your best bet is probably to use a union query such as the following.

set @param = '%new%';

select address, city, zip, 'address' as matched_on
from mls_data
where address like @param
union all
select address, city, zip, 'city' as matched_on
from mls_data
where city like @param
union all
select address, city, zip, 'zip' as matched_on
from mls_data
where zip like @param;


This will return multiple rows if more than one column matches (e.g. both the city and the address contain "new") indicating that multiple columns matched.

Example results: http://sqlfiddle.com/#!9/b814a/4

Update

After clarification of the question in the comments, I think this query may be what you're looking for:

declare @param varchar(max) = '%new%'

select mls, cast(address as varchar(max)) as [value]
from mls_data
where address like @param
union all
select mls, cast(city as varchar(max))
from mls_data
where city like @param
union all
select mls, cast(zip as varchar(max))
from mls_data
where zip like @param;


This returns two columns, the row ID and the matching value, regardless of which column the value comes from.

Example: http://sqlfiddle.com/#!3/ea1c9/5