-
phonetic searching in database??
I think i have written the correct title for this problem. SOrry if its wrong.
Wat I wanted to accomplish is that ... let us say i am have given something like 'ment' as search parameter... now I want to search words sounding similar to it to list as resultset along with the one i have searched....
lets say 'ment' is not in the database... then if there are 'mint', 'met', or 'men' in the database these shud be returned..
Let us say .. if i query for "computer" I want to result results like computer, comptr, chomputer, komputer,compute... etc... according to relevancy....
Is it possible to achieve thru full text search... if yes ... can it be done without it too only thru a stored proc or sql statement.??
I have seen this feature in some websites... so experts plz gimme some idea on it...
THanking you in advance..!!
-
it is for sql server 2000,
Originally Posted by zathrone
I think i have written the correct title for this problem. SOrry if its wrong.
Wat I wanted to accomplish is that ... let us say i am have given something like 'ment' as search parameter... now I want to search words sounding similar to it to list as resultset along with the one i have searched....
lets say 'ment' is not in the database... then if there are 'mint', 'met', or 'men' in the database these shud be returned..
Let us say .. if i query for "computer" I want to result results like computer, comptr, chomputer, komputer,compute... etc... according to relevancy....
Is it possible to achieve thru full text search... if yes ... can it be done without it too only thru a stored proc or sql statement.??
I have seen this feature in some websites... so experts plz gimme some idea on it...
THanking you in advance..!!
Sorry i forgot to mention i wanted to accomplish this in sql server 2000...
-
use SOUNDEX function
create table a(name varchar(100))
insert into a select 'ment'
insert into a select 'meant'
insert into a select 'met'
insert into a select 'men'
insert into a select 'computer'
insert into a select 'comptr'
insert into a select 'chomputer'
insert into a select 'komputer'
insert into a select 'compute'
declare @searchstring varchar(100)
set @searchstring ='computer'
select * from a where soundex(@searchstring)=soundex(name)
--result
computer
comptr
chomputer
compute
declare @searchstring varchar(100)
set @searchstring ='ment'
select * from a where soundex(@searchstring)=soundex(name)
--result
ment
meant
-
thanks for the reply
hello! Mak,
What is the difference between soundex and difference functions.??
can u plzz elaborate it with the help of an example.??
thanks for all ur support these days anyway..!
-
in addition
Originally Posted by zathrone
hello! Mak,
What is the difference between soundex and difference functions.??
can u plzz elaborate it with the help of an example.??
thanks for all ur support these days anyway..!
is it possible to get the relevancy percentage too???
thank you
-
from BOL. The integer returned is the number of characters in the SOUNDEX values that are the same. The return value ranges from 0 through 4, with 4 indicating the SOUNDEX values are identical. Use the return number are relevance. 0,1,2,3,4. 4 being very identical.
declare @searchstring varchar(100)
set @searchstring ='compute'
select * from a where difference(@searchstring,name)>2
--returns
computer
comptr
chomputer
komputer
compute
declare @searchstring varchar(100)
set @searchstring ='compute'
select * from a where difference(@searchstring,name)>3
--returns
computer
comptr
chomputer
compute
-
Thanks for everything MAK
if possible plz gimme some info on getting the relevancy percent on each result...
Thanks a lot
-
I dont think we can break down the percentage granular
Try this..
create table a(name varchar(100))
insert into a select 'ment'
insert into a select 'meant'
insert into a select 'met'
insert into a select 'men'
insert into a select 'computer'
insert into a select 'comptr'
insert into a select 'chomputer'
insert into a select 'komputer'
insert into a select 'compute'
insert into a select 'comp'
insert into a select 'com'
insert into a select 'post'
insert into a select 'Zurich'
declare @searchstring varchar(100)
set @searchstring ='compute'
select name,Relevance=difference(@searchstring,name)*1.00 , [Relevance %] =case difference(@searchstring,name)
when 4 then '100%'
when 3 then '75%'
when 2 then '50%'
when 1 then '25%'
when 0 then '0%' end
from a
-
or if you need granualar, you could write your own soundex function including vowels.
refer this.
http://www.ibphoenix.com/main.nfs?a=...age=ibp_howto4
-
Thanks MAK
U r simply superb... these are quite useful for me....
thanks
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|