New DMF for SQL Server 2008 sys.dm_fts_parser to parse a string

By: Jugal Shah | Read Comments | Print Jugal has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2. Related Tips: More

Problem

Many times we want to split a string into an array and get a list of each word separately. The sys.dm_fts_parser function will help us in these cases. More over, this function will also differentiate the noise words and exact match words. The sys.dm_fts_parser can be also very powerful for debugging purposes. It can help you check how the word breaker and stemmer works for a given input for Full Text Search.

Solution

In SQL 2008 and forward, with Integrated Full Text Search (iFTS) we can now very easily split words in an array of strings with the help of the dynamic management function sys.dm_fts_parser. This function takes a full text query and breaks it up using the word breaker rules, applies stop lists and any configured thesaurus.

Permission Required
This requires membership of the sysadmin fixed server role and access rights to the specified stoplist.

Syntax
sys.dm_fts_parser('query_string', lcid, stoplist_id, accent_sensitivity)

Examples

FORMSOF( THESAURUS, query_string)

You can check how the thesaurus expands or replaces all or part of the input using the below query

SELECT *   FROM sys.dm_fts_parser ('FORMSOF( THESAURUS, "Management Studio")', 2057, 0, 0)  

FORMSOF( INFLECTIONAL, query_string)

To check how the word breaker and the stemmer parse a query term and its stemming forms, you can execute the below query.

SELECT  *  FROM sys.dm_fts_parser ('FORMSOF( INFLECTIONAL, "Operating System")', 2057, 0, 0)  

Output

sys.dm_fts_parser('query_string', lcid, stoplist_id, accent_sensitivity)

This query will split the words in a string.

SELECT *  FROM sys.dm_fts_parser ('SQL or MySQL or Oracle or DB or technologies or the or dbservers', 1033, 0, 0)  

Next Steps

• Use the special character in the query string and test the output
• Use this function with a table using a Cross Apply join

Share:

Share

Tweet

Free SQL Server Learning The SQL Server Security THREAT - It’s Closer Than You Think Tutorial - SQL Server Performance Tuning and Monitoring

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code