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 thesaurus feature in SQL FTS. This is supported in SQL
2005, and unsupported but implemented for FreeText search in SQL 2000.

Show quoteHide quote

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/

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'

Show quoteHide quote

Fabio Cavassini wrote:
Show quoteHide quoteHave 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

Fabio Cavassini wrote:
Show quoteHide quoteAs well as this:
http://techrepublic.com.com/5102-9592-5716625.html

and where are Listing A and Listing B ?

regards, Robert

Robert Fuchs wrote:
http://techrepublic.com.com/5100-9592_11-5716625.html?tag=search

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.

Show quoteHide quote

Lot of interesting stuff...I'll need some time to review it...and then
I'll tell you the results I get...

Best Regards
Fabio Cavassini

generic audit trail best practice questions
append a string to all the values in a column SQL
Extracting from linked tables into pivot
What's a good SQL server naming convention
Optimizing an IN clause