Blog

Blog posts made on 04-Jan-07
4Jan
2007
Performing SQL Server CONTAINS Searches

SQL Server 2005 supports two forms of full-text search, FREETEXT and CONTAINS. CONTAINS is used to search for rows that contain words, phrases, partial phrases, words with the same stem, proximity searches, synonyms (using a thesaurus lookup), and more.

Here is a simple example:

view plain print about
1SELECT note_id, note_text
2FROM productnotes
3WHERE CONTAINS(note_text, 'handsaw');

WHERE CONTAINS(note_text, 'handsaw') means find the word handsaw in column note_text.

CONTAINS also supports the use of wildcards:

view plain print about
1SELECT note_id, note_text
2FROM productnotes
3WHERE CONTAINS(note_text, '"anvil*"');

'"anvil*"' means match any word that starts with anvil. Note that unlike LIKE, full-text searching uses * as the wildcard character (instead of %). Wildcards may be used at the beginning or end of a string. Also, when passing simple text to CONTAINS then that text is enclosed within single quotes. When passing wildcards each search phrase must be enclosed within double quotes inside those outer single quotes. Failing to do this will likely cause your searches to return no matches.

CONTAINS also supports Boolean operators AND, OR, and NOT. Here are a couple of examples:

view plain print about
1SELECT note_id, note_text
2FROM productnotes
3WHERE CONTAINS(note_text, 'safe AND handsaw');
4
5SELECT note_id, note_text
6FROM productnotes
7WHERE CONTAINS(note_text, 'rabbit AND NOT food');

When searching through extremely long text there is a greater likelihood of matches being found if search terms are near each other in the saved data. A simple AND search matches terms anywhere in the text, but NEAR can be used to instruct the full-text search engine to only match terms when they are close together. Here is an example:

view plain print about
1SELECT note_id, note_text
2FROM productnotes
3WHERE CONTAINS(note_text, 'detonate NEAR quickly');

'detonate NEAR quickly' means match only rows that contain the words detonate and quickly near each other.

Sometimes you may want to match a word that is part of the same family (based on the same stem). For example, if you were searching for "life" you'd also want to match "lives". Obviously, a wildcard of life* could not help here, and using li* would likely match too many false positives. This is where inflectional matching helps. Here is an example:

view plain print about
1SELECT note_id, note_text
2FROM productnotes
3WHERE CONTAINS(note_text, 'FORMSOF(INFLECTIONAL, life)');

'FORMSOF(INFLECTIONAL, life)' instructs the full-text engine to look for any words that share the same stem as the specified word, in this case "life".

FORMSOF() also supports THESAURUS searches, where words can match synonyms. To use this functionality you must first populate an XML thesaurus file with words and their synonyms.

Read More ›

4Jan
2007
Performing SQL Server FREETEXT Searches

FREETEXT provides a simple mechanism by which to perform SQL Server 2005 full-text searches, matching by meaning as opposed to exact text match. Here is a simple example:

view plain print about
1SELECT *
2FROM my_table
3WHERE FREETEXT(column1, 'rabbit food');

FREETEXT(column1, 'rabbit food') means perform a FREETEXT lookup on column column1 looking for anything that could mean rabbit food (but not necessarily those two exact words, and not necessarily as a phrase).

You can also search across all columns indexed for full-text search by using FREETEXT(*, 'search text').

If double quotes surround a search term then that exact phrase is matched, not the meaning.

Read More ›

4Jan
2007
Setting Up SQL Server 2005 Full-Text Searching

SQL Server 2005 features an integrated full-text search engine, which several people have e-mailed me to ask about recently. (ColdFusion users can use <cfquery> to perform queries using SQL Server full-text searches).

If you want to play with SQL Server 2005 full-text search capabilities, you need to do the following:

1) Once a database has been created, support for full-text must be enabled before any full-text operations can be performed. To enable full-text support the sp_fulltext_database stored procedure is used. This stored procedure updates the currently selected database, so be sure to USE the correct database before issuing this statement:

view plain print about
1EXEC sp_fulltext_database 'enable';

If you are using SQL Server Management Studio and use the interactive New Database dialog to create your database, you can check the "Use full-text indexing" checkbox, which causes the above mentioned stored procedure to be automatically executed.

2) SQL Server stores full-text data in a catalog (a file that needs to be created). A single catalog can be used for multiple tables and indexes. If a catalog does not already exist, create one using CREATE FULLTEXT CATALOG:

view plain print about
1CREATE FULLTEXT CATALOG my_catalog;

This creates a catalog named my_catalog in the default catalog location. To specify the actual file location the IN PATH attribute can be specified.

3) Once a catalog has been created you can define the actual full-text indexes for each table containing columns that you want searchable. Indexes are created using CREATE FULLTEXT INDEX like this:

view plain print about
1CREATE FULLTEXT INDEX ON table(column)
2KEY INDEX table_primary_key
3ON my_catalog;

When creating a full-text index you specify the table and column to be indexed as table(column). More than one column may be indexed if needed, to do this simply specify the column names (comma delimited). The key with which to uniquely identify rows is required, and so KEY INDEX is used to provide the name of the table's primary key. And finally, the ON clause specifies the catalog to be used to store full-text data, and here the just created catalog is used (unless a default catalog has been defined).

Once set up, you'll be ready to use the CONTAINS and FREETEXT functions in your WHERE clauses to perform full-text searching.

Read More ›

4Jan
2007
Finally Caught Up With Picture Posts

My On The Road page has finally been brought up to date (just in time to hit the road again).

Read More ›

4Jan
2007
ColdFusion Script To Manage Client Variable Registry Keys

I am not a big fan of storing CLIENT variables in the registry (I thought doing so was a hack when we first introduced CLIENT variables close to a decade ago, and still think that using the registry as a data store was a mistake). Actually, I am not a big fan of CLIENT variables altogether.

But having said that, a user contacted me with a problem. One of his servers contained so much CLIENT data that the registry had grown to several gig and Windows boot time was taking 50 minutes or so, and he wanted to know how to clear that data. Those numbers seem incredibly high, worryingly so, and I suspect that it's not just all ColdFusion CLIENT storage. Nevertheless, I sent him this link to a ColdFusion script that we posted (back in 2005) that does exactly that, it purges registry keys (and can also list and create them for testing).

Read More ›