I have a table named infoone with two columns address and locationCITY.
I want to write a search query.
If user searches for Edmonton, it should return all records that have city Edmonton.
If user searches for 12 main street Edmonton, then it should return all respective records. 12 main street is the address and Edmonton is the city
What query I have is following,
string sql = "select PLACEID,LEFT(CONVERT(NVARCHAR(1000),description),500)+'...' as des1,LOCATIONCITY,ADDRESS,DateT,RENT from infoone where ";
sql += "(((address like '%"+txtSearch.Text+"%') or (address like '')) and ((locationcity like '%"+txtSearch.Text+"%') or (locationcity like '')) and ((address LIKE '%"+txtSearch.Text+"%') or (address like '')))";
Above query is not returning anything when I search:
main street Edmonton
User will also be allowed to search without city like: 12th main street
What I am doing wrong?
Please help
Thanks
Free form searching for addresses is very difficult.
Let's look at your examples
Address locationCity
12 Main Street Edmonton
456 Thomas Ave St Martin
Possible searchs
I would suggest that your interface accepts two columns, one for address and one for city, it will make searching much easier.
where <other conditions>
AND
(locationcity like '%CitySearchFld%' and address like '%AddresssSearchFld%')
No need to search for empty, because if the user leaves the field blank, a search of %% will match all rows
Other considerations
What happens in the user searches for
12 Main St
or
Edmenton
Abbreviations? Misspellings?
To handle abbreviations, I would build a stop-word list, which would remove common abbreviations from the address field, things like St, Street, Avenue, Ave, etc. So the search becomes
12 Main
I'd hate to miss a record because I wasn't sure if it was st or Street in the table.
You can also use a function know as Soundex (native SQL) or Metaphone (custom SQL or CLR) to deal with misspellings...
Good luck
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加