CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsql-server-2012sql-like
fc123
fc123

Reputation: 918

sql search query with like operator

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

Upvotes: 0

Views: 490

Answers (3)

Silas
Silas

Reputation: 144

I don't believe your where clause is doing what you've intended. Let's remove the outer string var and reformat for readability:

select PLACEID,LEFT(CONVERT(NVARCHAR(1000),description),500)+'...' as des1
,LOCATIONCITY,ADDRESS,DateT,RENT 
from infoone 
where(
    ((address like '%"+txtSearch.Text+"%')  or (address like '')) 
    -- #1 address must match full text or be blank
    and 
    ((locationcity like '%"+txtSearch.Text+"%') or  (locationcity like '')) 
    -- #2 locationcity must match full text or be blank
    and 
    ((address LIKE '%"+txtSearch.Text+"%')  or (address like ''))
    -- #3 address must match full text or be blank. Seems a duplicate of #1
    )

These three are chained together with ANDs, so all three conditions must be true for it to return a result.

At the very least, the Where clause might be re-written to be this:

select PLACEID,LEFT(CONVERT(NVARCHAR(1000),description),500)+'...' as des1
,LOCATIONCITY,ADDRESS,DateT,RENT 
from infoone 
where(
    (address like '%"+txtSearch.Text+"%')  
    or 
    (locationcity like '%"+txtSearch.Text+"%')
    or
    (address + locationcity like '%"+txtSearch.Text+"%')
    or
    (address + ' ' + locationcity like '%"+txtSearch.Text+"%')
    )

This will return a record if a text match was found in either address or locationcity, or if the text matches them when combined, or combined with a space.

This is why you are not getting any results back on the sample input you provided. With the above code, you should get a match on the final fourth condition when searching for "main street edmonton"

You should get your code working with non-dynamic SQL in SSMS first, using a variable for your text (e.g. @TEXT), and once it is returning the results you want based on your text param, you can switch it over to dynamic. Writing this as a SQL string to execute is just complicating things while you're still working on developing your SQL code.

For a broader solution, you may want to look at Full Text Search: http://msdn.microsoft.com/en-us/library/ms142571.aspx

This breaks apart a string into individual words, and searches for those words, it allows you to come up with weighted guesses and rankings of match. It even lets you use similar terms using thesaurus, so if someone searched for "123 Main St." and "123 Main Street" was in the database, it would find those as a match.

Upvotes: 1

ABAS
ABAS

Reputation: 1

this kind of search is so difficult, but there are many different way to make good search in address,

the easiest way is, search in complete address like this;

select * from adrs WHERE (locationcity + ',' + address) like '%searchword%'

but there is no way to know which word is city or not.

Upvotes: 0

Sparky
Sparky

Reputation: 15115

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

  • Edmonton - If only 1 word, should we assume it is a city? If so, how would the user find St. Martin?
  • 12 Main Street Edmonton - Now, how do you know Edmonton is the city? Could the user search for just a street name?

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

Upvotes: 2

Related Questions