"did you mean" feature

Having a list of words in a table...haw can I make with SQL a "did you
mean" search?
for example..
Having a table with these data:
hello
hallo
hi
lup
hai
If I look for "hollo" it should return "hello", "hallo"
I know this could be a very complex algorithm, but I'm looking for it's
simplest form that could be implemented with Transact SQL
Best Regards
Fabio Cavassini
http://www.pldsa.comHave a look at the thesaurus feature in SQL FTS. This is supported in SQL
2005, and unsupported but implemented for FreeText search in SQL 2000.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fabio Cavassini" <cavassinif@.gmail.com> wrote in message
news:1137181586.850037.17240@.g43g2000cwa.googlegroups.com...
> Having a list of words in a table...haw can I make with SQL a "did you
> mean" search?
> for example..
> Having a table with these data:
> hello
> hallo
> hi
> lup
> hai
> If I look for "hollo" it should return "hello", "hallo"
> I know this could be a very complex algorithm, but I'm looking for it's
> simplest form that could be implemented with Transact SQL
> Best Regards
> Fabio Cavassini
> http://www.pldsa.com
>|||Are saying that you want to select all rows where key values resemble a
given parameter?
There is the LIKE operator for performing truncated, wildcard, or
substitution type comparisons. For example:
select * from words where word like 'hel%'
select * from words where work like 'h_ll_'
There is also the SoundEx() function which accepts a character string and
returns a condensed code based on the string's phoenic spelling. For
example:
select * from words where word = SoundEx('hello')
print soundex('hello')
H400
print soundex('hallo')
H400
print soundex('half')
H410
This is also useful when searching on first or last names.
The most accurate solution would be to have a referece table which maps each
word to 0 - many equivalent words.
select word from Thesaurus where synonym = 'hello'
"Fabio Cavassini" <cavassinif@.gmail.com> wrote in message
news:1137181586.850037.17240@.g43g2000cwa.googlegroups.com...
> Having a list of words in a table...haw can I make with SQL a "did you
> mean" search?
> for example..
> Having a table with these data:
> hello
> hallo
> hi
> lup
> hai
> If I look for "hollo" it should return "hello", "hallo"
> I know this could be a very complex algorithm, but I'm looking for it's
> simplest form that could be implemented with Transact SQL
> Best Regards
> Fabio Cavassini
> http://www.pldsa.com
>|||Fabio Cavassini wrote:
> Having a list of words in a table...haw can I make with SQL a "did you
> mean" search?
> for example..
> Having a table with these data:
> hello
> hallo
> hi
> lup
> hai
> If I look for "hollo" it should return "hello", "hallo"
> I know this could be a very complex algorithm, but I'm looking for
> it's simplest form that could be implemented with Transact SQL
> Best Regards
> Fabio Cavassini
> http://www.pldsa.com
Have a look at the soundex function, and consider storing the soudnex values
in the table to avoid table scan operations each time. Best to do this from
the stored procedure that does the inserting:
create table SoundexTest (Col1 varchar(30), Col1Soundex char(30))
create index Col1SoundexIDX on SoundexTest (Col1Soundex)
Insert into SoundexTest Values ('Hello', SOUNDEX('Hello'))
Insert into SoundexTest Values ('Hallo', SOUNDEX('Hallo'))
Insert into SoundexTest Values ('Hey', SOUNDEX('Hey'))
Select * from SoundexTest where Col1Soundex = SOUNDEX('Hallo')
drop table SoundexTest
David Gugick
Quest Software|||Fabio Cavassini wrote:
> Having a list of words in a table...haw can I make with SQL a "did you
> mean" search?
> for example..
> Having a table with these data:
> hello
> hallo
> hi
> lup
> hai
> If I look for "hollo" it should return "hello", "hallo"
> I know this could be a very complex algorithm, but I'm looking for
> it's simplest form that could be implemented with Transact SQL
> Best Regards
> Fabio Cavassini
> http://www.pldsa.com
As well as this:
http://techrepublic.com.com/5102-9592-5716625.html
David Gugick
Quest Software|||This will work for you
it will be expensive (table scan etc) but it's the simplest form
you could put it in a UDF
add i, u and y for the complete set
create table testString (value varchar(55))
insert into testString
select 'hello' union all
select 'hallo' union all
select 'hi' union all
select 'lup' union all
select 'hai'
declare @.Value varchar(55)
select @.value = 'hollo'
select * from testString
where replace(replace(replace(value,'e',''),'a
',''),'o','') =
replace(replace(replace(@.value,'e',''),'
a',''),'o','')
http://sqlservercode.blogspot.com/|||> As well as this:
> http://techrepublic.com.com/5102-9592-5716625.html
and where are Listing A and Listing B ?
regards, Robert|||Robert Fuchs wrote:
> and where are Listing A and Listing B ?
> regards, Robert
http://techrepublic.com.com/5100-95...html?tag=search
David Gugick
Quest Software
www.quest.com|||>> know this could be a very complex algorithm <<
OH YES!! Do not do this kind of search in SQL; use a textbase tool
which has the complex algorithms tuned for a particlar natural
language, like English.|||What like the Full-Text search facilities that are already built into SQL
Server and allow you to join returned documents from your text search
against your schema - extremely powerful i'd say.
Really, have a look at the complete feature list that is offered in SQL
Server, its not just a relational data storage and retrieval engine.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137736598.084973.118370@.z14g2000cwz.googlegroups.com...
> OH YES!! Do not do this kind of search in SQL; use a textbase tool
> which has the complex algorithms tuned for a particlar natural
> language, like English.
>