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.

5 thoughts on “SQL Server 2008: Interesting Full-Text Dynamic Management Function”

  1. 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

  2. @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.

Leave a Reply

Your email address will not be published. Required fields are marked *