Sql-server – Single query for searching for records without knowing what the search column is for

sql-server-2012

I have a single line search where i should be able to search for address or city or zip or state. I should be able to show data of what ever that matches for the typed in word. Suppose user had typed in new york then I have to write an sql query which should give the output like

SearchResult new York(coming from city)

123 main street New York (coming from address)

.

. .

If i type in 58986

SearchResult

123 main street houston 58986 (coming from address data)

58986 (coming from zip column data)

.

.

I have tried writing like this 

select sd_name,zip from mls_data where sd_name like '%new%' or zip like 'new%'

but its not working

table mls_data

mls   address                   city            zip
1   56 Main Street, New York    New York        123456
2   10178 Berlin, Germany       Berlin          566996
3   5th Street, USA             Washington      878788
4   3rd Street, Washington      Washington      589345


input parameter for query 

washington

result

mls     alisasName
4   3rd Street, Washington
4   Washington
3   Washington

Best Answer

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

Related Question