Full Test Search Capability Question

I need to search a data base column in a table for a "fuzzy match" (we are using SQL Server 2005).  We have a table of organizations and the user searches entering the organization name in a form field.

For example
Someone keys in an organization name of :
Microsift (misspelled)

I don't think Full Text Search handle "typos".  So Microsoft would not be found.

Note:  If you key this into most search engines, they come back with:
Showing results for microsoft
They have the "fuzzy logic" search that I'd like to use on a table.

The server side code is in ColdFusion

Any sugesstions?

Thanks in advance,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
" If you key this into most search engines"

That IS - SQL full-text is not a Search Engine and indeed does not have spell checker inclus so the users better know what they want to search for :)

Besides this please keep in mind that there are some "noise" words per language and if some search does not return the expected result you can check/edit and eventualy remove that noise word from the file.
hefterrAuthor Commented:
Hi lcohan
If I used FTS using :
The Intermxational Business Machine

and our column had :
International Business Machine Corporation

Would the row be returned as it matched 2 other words and ignores the noise word of "the"?

I haven't installed and configured FTS and the docs are not clear to me.

lcohanDatabase AnalystCommented:
<<I haven't installed and configured FTS and the docs are not clear to me.>>

Sorry to say but in my opinion you must do a bit of research and testing of your own because no BOL will give you a clear answer if your search on "The Intermxational Business Machine" returns something or not.

I have full text installed but not a "International Business Machine" customer in my database. Is just IBM and that does not show in my search for "The Intermxational Business Machine"

I think that Full-Text installation/configuration for one test table may take you less time than it took me to type this message.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

hefterrAuthor Commented:
Were do I start with the SQL Server Management Studio (of a cook book web page)?
Coldfusion comes with Verity Search.. this will give you text searching - I don't know if it will correct misspelled words or not, but it will handle searching multiple terms (phrases) by relevance and such...
lcohanDatabase AnalystCommented:
You should check if your DB is FULL text enabled:

select DATABASEPROPERTY(db_name(), 'IsFulltextEnabled')

if result is 0 or not enabled run the code below against that DB

use database_name
--      Enable Database for full text
if DATABASEPROPERTY(db_name(), 'IsFulltextEnabled') = 0
      EXEC sp_fulltext_database 'enable'

If result is 1 or enabled you can go in SSMS expand the db tables and pick one that has data you want to search on. Then right click that table, and select Full Text from the drop down and go from there

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hefterrAuthor Commented:
If a user keys in a phrase (name of an organization), I'd like to try and match on any of the words (except the noise words).  All the docs I see for CONTAINS, you have to separate the words yourself with AND or OR.  Is FREETEXT better in this situation?

Can you give me a quick overview of Verity Search?   I never heard of it.  Is it for docs , DBs or both?

Verity is a search engine that uses text searching - you build a "collection" (the search index) against database tables or physical files (office files, PDF, etc).    Use commands such as cfcollection and cfsearch (if I recall correctly) to perform searches and return a query of records as a result.   Verity allows for use of operators such as AND, OR and can take a phrase and find matches of one or more words not necessarily in the order the were entered (you know, like Google, Yahoo, etc)

I don't know about searching incorrectly spelled words though... that's a maybe..
hefterrAuthor Commented:
Thanks for the CF tip!  It does sound a lot like SQL Server "Full Text Search".  I'll look at both of them.

It would be nice if Yahoo or Google let you get at how they reform your request.  They always seem to know exactly what you meant on the search request.

Thanks to both.  I'll have to experiement.

hefterrAuthor Commented:
lcohanDatabase AnalystCommented:
""FREETEXT Is a predicate used to search columns containing character-based data types for values that match the meaning and not the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally "word-breaks" the freetext_string into a number of search terms and assigns each term a weight and then finds the matches."

in other words FREETEXT will search the words that are with the same meaning VS CONTAINS which it will search only for that specified word in that column.

Even if it's older version description and the way it works still applies:


hefterrAuthor Commented:
Hi lcohan,
In my limited experiementation with FREETEXT, it seems to just break up your search phrase, eliminate "noise words" and then return any row that contains any of the words in the search column.

I used a table in AdventureWorks that only had 4 rows, but all I had to do was create a search phrase that had a word from each row.

Just and FYI.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.