phonetic searching in database??

[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..!!

[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...

[MAK]

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

[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..!

[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

[MAK]

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

[zathrone]

if possible plz gimme some info on getting the relevancy percent on each result...


Thanks a lot

[MAK]

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

[MAK]

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

[zathrone]

U r simply superb... these are quite useful for me....

thanks