Search, No results, Give Suggestion

[tinapa]

Hi guys, I have a Search and Result pages in asp.net. If the user searched for a word but if he typed it in wrong spelling(e.g. tommorow), I want to show him that there's no record found in the database AND give him word suggestion/s(e.g. did you mean tomorrow? -- which has entry in our database)

I have this table fully-text-indexed.

How do I write the SQL statement for that? Any ideas and advice is appreciated.

Cheers

 

[mrdenny]

You'd need a table with misspellings in it and the correct spelling so that you know what words to display. Then simply query the table for the incorrect spelling and display the correct one.

The trick would be how to make the system learn the incorrect spellings. You'll need to keep track of the incorrect search attempts and the correct search attempts and have someone review them to ensure that the words are actually the correct spellings.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[tinapa]

thanks for your advice mrdenny, i took note of it.

but is there any function in the sql server full text indexing that somehow does similar function?

cheers

 

[mrdenny]

No, there's no spell checking built in to SQL Server full text search.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[RiverGuy]

There's nothing in SQL Server to suggest alternate words. I'm sure you can buy a database of words and their frequent misspellings somewhere. Or there may even be something in the public domain. If you want to work on implementing your own algorithm as Mr. Denny suggested, you might look at the SOUNDEX and DIFFERENCE functions. Look them up in Books Online.

DECLARE @String1 VARCHAR(100)
DECLARE @String2 VARCHAR(100)
DECLARE @String3 VARCHAR(100)

SELECT @String1 = 'tomorrow'
SELECT @String2 = 'tommorrow'
SELECT @String3 = 'thompson'


SELECT SOUNDEX(@String1) AS String1, SOUNDEX(@String2) AS String2, SOUNDEX(@String3) AS String3

SELECT DIFFERENCE(@String1, @String2) AS String1and2Diff, DIFFERENCE(@String1, @String3) AS String1and3Diff

@String1 is tomorrow. @String2 is a misspelling of tomorrow. @String3 is a different word. We know that @String2 was probably meant to be tomorrow, whereas @String3 was probably meant to be someone's name. We can tell how similar two strings might sound by using the SOUNDEX function. As you can see, @String1 and @String2 sound very similar, supposedly. @String3 does not really. The different between @String1 and @String2 is 4 (high), which means there is little difference. The opposite is true for @String1 and @String3.

So, to help with suggestions before you get a well-sampled list compiled, you might compare previous searches with the SOUNDEX/DIFFERENCE of the search term to suggest other possible search spellings.

 

[tinapa]

thanks guys, i will look into those.

have a nice day!

 

[jbenson001]

If you are using asp.net, you can use AJAX and autocomplete functionality on the textbox. There are many examples out there.

 

[tinapa]

Hi guys, I have found this:

http://www.tek-tips.com/faqs.cfm?fid=4110

How do I implement 'dbo.SoundexAplhaFunction' this if I want to apply this to a table? (e.g. myTable)

 

[RiverGuy]

Where you see the Code heading the author posted, you copy the code beneath it and run it against your server (run against a dev database at first). That's the definition of the function. That will create the function and store it in your database.

And then you utilize it like they've shown below under the Using heading.

 

[tinapa]

hi RiverGuy, i've ran the function but can't seem to apply it to my table.

can you show me a sample sql statement to query a table:

countryTable
---------------------------
id | country | description
---------------------------

thanks for your help.

 

[markros]

select * from Country table where
Decription = @SearchTerm or

dbo.SoundexAplhaFunction(description ) = dbo.SoundexAplhaFunction(@SearchTerm)

This is for exact one word search. If you want to implement partial word search, it may be much harder.

 

[mrdenny]

Now do keep and mind that using a function against a column in the where clause will greatly effect performance.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[RiverGuy]

Now do keep and mind that using a function against a column in the where clause will greatly effect performance.

That's a good point and also good reasoning to think about pre-calculating the Soundex/SoundexAplhaFunction values for terms which have already been searched on, storing the values in a table.

 

[tinapa]

thanks very much guys for all your advice and help.

i will keep those in mind.

have a nice weekend!