?
Solved

Full Test Search Capability Question

Posted on 2011-10-07
12
Medium Priority
?
284 Views
Last Modified: 2012-05-12
Hi,
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,
hefterr
0
Comment
Question by:hefterr
  • 6
  • 4
  • 2
12 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 36932555
" 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.
0
 
LVL 1

Author Comment

by:hefterr
ID: 36932884
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.

hefterr
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36933029
<<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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:hefterr
ID: 36933104
Were do I start with the SQL Server Management Studio (of a cook book web page)?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36933121
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...
0
 
LVL 40

Accepted Solution

by:
lcohan earned 1000 total points
ID: 36933172
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
go
--      Enable Database for full text
if DATABASEPROPERTY(db_name(), 'IsFulltextEnabled') = 0
      EXEC sp_fulltext_database 'enable'
GO

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
0
 
LVL 1

Author Comment

by:hefterr
ID: 36933731
@lcohan
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?

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

hefterr
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 1000 total points
ID: 36936064
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..
0
 
LVL 1

Author Comment

by:hefterr
ID: 36936218
@gdemaria,
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.

hefterr
0
 
LVL 1

Author Closing Comment

by:hefterr
ID: 36936227
Thanks!
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36950207
""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:

http://msdn.microsoft.com/en-us/library/aa258864(v=sql.80).aspx




0
 
LVL 1

Author Comment

by:hefterr
ID: 36952960
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.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question