Using SOUNDEX in Full Text Search query

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 2005 Forums

Transact-SQL (2005)

Using SOUNDEX in Full Text Search query

Author

Topic

sleb
Starting Member

3 Posts

Posted - 2007-02-26 : 12:50:18

Hi there

I have this query in a stored procedure

SELECT *
FROM Product
WHERE FREETEXT (*, @SearchString )

When I run the query with the @SearchString = 'hoover' it return values but if I spelt it wrong (eg: hover) I have no value return

My question is, How can I use the SOUNDEX in a full text search

Regards

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-02-27 : 05:34:16

as per BOL:

SELECT *
FROM Product
WHERE FREETEXT (Column Name, @SearchString )

instead of using * , use specific column for search the text.

let us know, anything else,

Mahesh

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-02-28 : 10:28:28

yes but that will not return the value 'hoover' if you type in 'hover' will it?

sleb
Starting Member

3 Posts

Posted - 2007-02-28 : 10:35:38

You're right dnf,
It still won't return values with spelling mistakes.

This is why I need to know if it's possible to use SOUNDEX function in Full Text Search

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-02-28 : 10:47:13

same here! i need to know this to.

Any SQL wizards out there that can help?

sleb
Starting Member

3 Posts

Posted - 2007-02-28 : 10:52:40

I keep looking here and there. If I find my answer I'll let you know. Please do the same thing

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-02-28 : 18:50:49

Here is a function I developed which is much better than soundex at identifying spelling differences:

create function CompareText  (@String1 varchar (50), @String2 varchar (50))
returns integer
--Function CompareText
--blindman 4/2005, Adapted from MS Access algorithm developed 1997
--Returns value between 0 and 100 indicating the similarity between two character strings.

--usage: select * from [Table] where dbo.CompareText([ColumnValue], 'SearchString') > 80

begin

declare @Possibles integer
declare @Hits integer
declare @Counter integer

set @Possibles = len(@String1) + len(@String2) - 2
set @Hits = 0

set @Counter = len(@String1)-1
while @Counter > 0
	begin
	  if charindex(substring(@String1, @Counter, 2), @String2) > 0 set @Hits = @Hits + 1
	  set @Counter = @Counter - 1
	end

set @Counter = len(@String2)-1
while @Counter > 0
	begin
	  if charindex(substring(@String2, @Counter, 2), @String1) > 0 set @Hits = @Hits + 1
	  set @Counter = @Counter - 1
	end

return (100*@Hits)/@Possibles
end

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-03-01 : 05:33:05

Apologies i'm quite new to the use of user made functions.

How do you run this?

And also can the function be amended so that if you search a particular field for a word i.e 'company'

It can find misspelt and similiar words?? i.e. campany, cmpny, compony etc...

Thanks!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-03-02 : 14:02:45

Run the script to create the function.

Call the function like this syntax:

select * from [Table] where dbo.CompareText([ColumnValue], 'SearchString') > 80

You can adjust the sensitivy by changing the cutoff criteria from 80 to whatever you want. Higher values are more selective, and lower numbers are less selective.

select dbo.CompareText('Hoover', 'Hover') --Returns a value of 88 (Good match...)
select dbo.CompareText('Company', 'campany') --Returns a value of 66 (Questionable match...)
select dbo.CompareText('Company', 'cmpny') --Returns a value of 40 (Very low...)
select dbo.CompareText('Company', 'compony') --Returns a value of 66 (Questionable match...)

Note that this function compares entire strings. It will not, for instance, find the word "Hoover" in the string "Yesterday I broke my Hover vacuum sweeper."

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources