Fuzzy search using SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Fuzzy search using SQL

Author

Topic

amitbadgi
Starting Member

29 Posts

Posted - 2005-07-14 : 13:05:23

Hi guys, I have created a form in access which is used to search a access database. There are 2 cities and each of em has 2 access tables, I have successfully written the sql query and attached it to a macro which in turn attached it to the search button on my form. Now my question is that I want to enable teh fuzzy search for my form, which is , if a user enters a Address, say 1547, he shld be able to get all the following matches from the database
1547 bulldog
1547 wshaw
1547 cedar
and so on....
this is the query that I have written that takes in user input and displays the result. Currently I have to enter the exact address etc, else it wouldnt work and I want teh fuzzy search to work.

SELECT OneWorld.bus_id, OneWorld.bus_nam, OneWorld.bus_add, OneWorld.city, OneWorld.state, OneWorld.zip, OneWorld.phone, OneWorld.license
FROM [Select Distinct dbo_businessNC.bus_id as bus_id,dbo_businessNC.bus_name as bus_nam,dbo_businessNC.adrs1 as bus_add,dbo_businessNC.city as city,dbo_businessNC.state as state,dbo_businessNC.zip as zip, dbo_businessNC.phone as phone, dbo_occhistoryNC.license as license from dbo_businessNC, dbo_occhistoryNC WHERE ( dbo_businessNC.bus_id=dbo_occhistoryNC.bus_id)
UNION ALL Select bus_id, bus_name as bus_nam, bus_add, city, state, zip, phone, license from Hawaccsumm
]. AS OneWorld
WHERE (((OneWorld.bus_id) = [Forms].[Form1].[Text2])) OR (((OneWorld.bus_nam) =[Forms].[Form1].[Text5])) OR (((OneWorld.bus_add)= [Forms].[Form1].[Text8])) OR (((OneWorld.phone) =[Forms].[Form1].[Text10])) OR (((OneWorld.zip)=[Forms].[Form1].[Text15]));

Thanks in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-14 : 13:14:56

[code]
declare @t table (city varchar(50))
insert into @t
select '1547' union all
select '1547 vgdsfg' union all
select '1547 ffeea' union all
select '1547 fewfea' union all
select '1548 hjtrhde' union all
select '1548 ewgw'

select city
from @t

select city
from @t
where city like '1547%'
[/code]

Go with the flow & have fun! Else fight the flow

amitbadgi
Starting Member

29 Posts

Posted - 2005-07-14 : 13:26:02

Hi, Thanks for your reply, but there are a vast number of records(there are more than 5000 adresses), and not only do I want it to work for the address part but for teh rest of the options i have on the form, like BID, Phone number etc, now I cannot write in the query as u suggested for all the records, I am hoping that i am getting it right. The user inputs any of teh options and he should get all teh related data from the tables.

Kristen
Test

22859 Posts

Posted - 2005-07-14 : 13:33:06

Change the WHERE clause to do the euqivalent of:


DECLARE @strSearch varchar(8000)
SET @strSearch = '%' + '1547' + '%'

WHERE     city like @strSearch
      OR bus_nam like @strSearch
      OR bus_add like @strSearch
      OR phone like @strSearch
      OR zip like @strSearch

on 5000 records SQL will do this "broad" search in a split second.

Kristen

amitbadgi
Starting Member

29 Posts

Posted - 2005-07-14 : 13:39:42

Hello Kristen, Thanks for your reply, but i wanted to let you know that my form has 5 options, and teh user can enter any one of teh options and shld be able to get the desired result. The 5 options are address, BID, city, phone, zipcode. And by saying 1547, i meant that it could be anything, say, 1547, 3062, 1457 etc..it has different addresses. Hence if the user enters say a zip or a address or a BID, he shld get the result. I hope you are able to understand what i am trying to say. Coz teh query u sent me is specific to 1547, so can i do it for any address, zip, telephone etc. Please do let m eknow if, what i typed in isnt clear. Thanks in advance.

Kristen
Test

22859 Posts

Posted - 2005-07-14 : 13:47:30

Ah, OK. So you are saying that the Form Value for Address could be 1547, or that form field could be blank and the form field for City could have be "New York"?

Something like this then maybe:


WHERE     (@CitySearch = '' OR city like @CitySearch)
      AND (@BusNameSearch = '' OR bus_nam like @BusNameSearch)
      AND (@BusAddrSearch = '' OR bus_add like @BusAddrSearch)
      AND (@PhoneSearch = '' OR phone like @PhoneSearch)
      AND (@ZipSearch = '' OR zip like @ZipSearch)

Kristen

amitbadgi
Starting Member

29 Posts

Posted - 2005-07-14 : 14:20:48

Thanks for being so quick kristen , so how do i define the strings cityearch,zipsearch etc, coz in teh previous reply u had defined it this way,
DECLARE @strSearch varchar(8000)
SET @strSearch = '%' + '1547' + '%'

How do it for the above which u had suggested. Thanks.

amitbadgi
Starting Member

29 Posts

Posted - 2005-07-14 : 15:11:13

HEy thanks for your reply, I tried this and its working fine

WHERE (((OneWorld.bus_id) LIKE "*" &[Forms].[Form1].[Text2]& "*")) AND (((OneWorld.bus_nam) LIKE "*" &[Forms].[Form1].[Text5]& "*")) AND (((OneWorld.bus_add) LIKE"*" & [Forms].[Form1].[Text8]& "*")) AND (((OneWorld.phone) LIKE "*" &[Forms].[Form1].[Text10]& "*")) AND (((OneWorld.zip) LIKE "*" &[Forms].[Form1].[Text15]& "*"));

Kristen
Test

22859 Posts

Posted - 2005-07-14 : 16:26:11

Yup, that should work starting with your Form Fields.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources