0

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  
3
  • 2
    What, exactly, does "its not working" mean? Wrong results? Error message? Something else, what?
    – vonPryz
    May 19 '15 at 7:20
  • i should be able to get the result as a single column where the criteria has matched . But its giving zip = 123456 although i have passed "new york" as search param
    – Vivekh
    May 19 '15 at 9:28
  • So you are getting wrong results. Add table structure, sample data, query, expected and actual results to your post. Without those, it's all guessing from simple typo to an error in query logic.
    – vonPryz
    May 19 '15 at 9:54
1

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

3
  • I have tried the fiddle i want to know whether it works if there are matching data from bothe city and zip i mean more than one column
    – Vivekh
    May 19 '15 at 13:50
  • Sorry it's not clear to me what you're looking for. Essentially if the city column matches, it will return a row with 'city' in last column (matched_on). If the address also matches it will return another row with 'address' in the matched_on column.
    – Matt
    May 19 '15 at 14:10
  • Yes but I would be more intrested in getting the results as a single column data. So I would say it doesn't matter which column it has matched but it should output the result as a single column data as I shown In my expected output
    – Vivekh
    May 19 '15 at 18:16
0

You can use a sort of UNPIVOT with CROSS APPLY VALUES, like this:

DECLARE @Addresses TABLE (
    [Address] nvarchar(100),
    [City] nvarchar(100),
    [ZIP] char(5),
    [State] nvarchar(100)
)

INSERT INTO @Addresses 
VALUES
('#500-75 O''Connor Street','Ottawa','K4B 1','Ontario'),
('#9900 2700 Production Way','Burnaby','V5A 4','British Columbia'),
('00, rue Saint-Lazare','Dunkerque','59140','Nord'),
('02, place de Fontenoy','Verrieres Le Buisson','91370','Essonne'),
('035, boulevard du Montparnasse','Verrieres Le Buisson','91370','Essonne'),
('081, boulevard du Montparnasse','Saint-Denis','93400','Seine Saint Denis'),
('081, boulevard du Montparnasse','Seattle','98104','Washington'),
('084, boulevard du Montparnasse','Les Ulis','91940','Essonne'),
('1 Corporate Center Drive','Miami','33127','Florida'),
('1 Mt. Dell Drive','Portland','97205','Oregon'),
('1 Smiling Tree Court','Los Angeles','90012','California')


SELECT A.*, 'Coming from ' + v.ColumnName AS ResultsFrom
FROM @Addresses AS A
CROSS APPLY (
    VALUES 
        (Address, 'Address'),
        (City, 'City'),
        (ZIP, 'ZIP'),
        (State, 'State')
) AS v(AddressColumn, ColumnName)
WHERE v.AddressColumn = 'Portland' 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.