THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

SQL Server 2008: Interesting Full-Text Dynamic Management Function

I've been playing around with full text search in SQL Server 2008 and was intrigued by the DMF sys.dm_fts_parser(). It allows you to see the result of the word-breaking occurring within full text search. If you execute the query:

select * from sys.dm_fts_parser('"Hello Greg. How are you? I haven''t seen you for a while."',1033,0,0);

It returns the following data (along with some other columns):

occurrence  special_term     display_term
1           Exact Match      hello
2           Exact Match      greg
10          End Of Sentence  END OF FILE
11          Noise Word       how
12          Noise Word       are
13          Noise Word       you
21          End Of Sentence  END OF FILE
22          Noise Word       i
23          Exact Match      haven't
24          Exact Match      seen
25          Noise Word       you
26          Noise Word       for
27          Noise Word       a
28          Noise Word       while
36          End Of Sentence  END OF FILE

(15 row(s) affected)

That's really sweet as it lets you parse text into words. The 1033 was the locale I chose and the other two parameters were a stop word list (formerly called a noise word list) and whether or not it should be accent sensitive.

Published Thursday, March 20, 2008 9:57 AM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



DNeimke said:

Thanks Greg, that's very cool.  

March 19, 2008 7:20 PM

AaronBertrand said:

I have yet to implement anything that uses FullText, but I agree with others, this seems very useful.

March 20, 2008 10:00 AM

jerryhung said:

so, is the only used by Full-Text?

or somehow we can use it as a string-split function as well? and filter out the noise word

March 20, 2008 11:39 AM

DNeimke said:

@jerryhung, I think that this is the cool thing.  Running that function will return a resultset which you could use for exactly the purpose that you've mentioned.

March 20, 2008 6:37 PM

df said:

But still no position information...sigh...

March 28, 2008 4:31 PM

Leave a Comment


This Blog



No tags have been created or used yet.


Privacy Statement