Let us spend some time with sys.dm_fts_parser

Introduction

Though it is a little old but still worth to know about the dynamic management function sys.dm_fts_parser which was introduced with Sql Server 2008.Let us understand the purpose of learning it.Suppose we have a string as under

www.youtube.com;imageshack.us;

And we need to break the words into all parts. The desired output we will be looking at is

www
youtube
com
imageshack
us

That is we need to get all the words separately into an array from a delimiter separated string. In such cases, this function will turn out to be very handy.

Another benefit of using the function is that it helps to differentiate the noise words and exact match words

Syntax

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

Where,

query_string: The string that we need to parse.

lcid:Locale identifier (LCID) of the word breaker.

stoplist_id:An integer value which is unique in the database and is used by the word breaker identified by lcid. The system stop-list will be turned on if we specify the value 0. Specifying a NULL will turn it off.

accent_sensitivity:It is a bit field where 0 is for Insensitive and 1 is for sensitive.

Examples

Case 1: Split a delimiter separated string

A few times back I wrote a small article on Split Function in Sql Server using Set base approach

We will see the same over here using this function>

Let us create an environment for the same

For a given input like

Name    MobileNumbers
Name1   1111111111,22222222222,3333333333,4444444444
Name2   5555555555,6666666666

We are looking for the below output

Solution with sys.dm_fts_parser

Declare @t Table (Name Varchar(10),MobileNumbers Varchar(50))
Insert Into @t Values
('Name1','1111111111,22222222222,3333333333,4444444444'),
('Name2','5555555555,6666666666')

SELECT Name,MobileNumber = display_term
FROM @t
CROSS APPLY sys.dm_fts_parser('"' + MobileNumbers + '"', 1033, 0,0)
where display_term NOT LIKE 'nn%'

The function returns many columns out of which we are interested only in the display_term where the precise words are listed. Then by using a cross apply we join it back to our original table in order to display the correct result.

Case 2: Split a multiple delimiter separated string

For a given input like

Data
www.youtube.com;imageshack.us;
www.youtube.com||imageshack.us.
www.youtube.com-http://spa.snap.com,

The needed output should be

Solution

Declare @t table(Data Varchar(50))
Insert into @t Values
('www.youtube.com;imageshack.us;'),
('www.youtube.com||imageshack.us.'),
('www.youtube.com-http://spa.snap.com,')

SELECT Data, SplitData = Display_Term
FROM @t
CROSS APPLY sys.dm_fts_parser('"' + Data + '"', 1033, 0,0)
where display_term NOT LIKE 'nn%'

As can be figure out that we have different delimiters at every row and the function still breaks them out and sends back the words available in the thesaurus.It would have been extremely difficult to write such a parser otherwise.

Case 3: Extract the numeric and non-numeric words

For a given input like

Data
This is 5 ,RollNumber is 222

The needed output should be

Solution

Declare @t table(Data Varchar(100))
Insert into @t Values
('This is 5 ,RollNumber is 222')


SELECT Data,Display_Term,IsNumeric = Case When ISNUMERIC(Display_Term) = 1 Then 'Numeric' Else 'Non-numeric' End
FROM @t
CROSS APPLY sys.dm_fts_parser('"' + Data + '"', 1033, 0,0)
where display_term NOT LIKE 'nn%'

Limitation

We cannot have the "query_string" parameter length more than 4000 character else it will throw error.E.g.

select display_term from sys.dm_fts_parser('"13557,34782,17331,11156,24333,34839,11928,22900,22179,40749,8507,51198,69060,40488,10843,25806,120504,8473,62768,32959,94632,90000,29615,1016,21681,14308,22962,88136,33567,2089,12070,17344,119921,39843,41031,92346,15096,47588,57771,98296,80974,86530,2092,351,17706,38841,52232,564,12917,44002,31599,41529,15187,2459,46591,76133,7155,12590,15663,41927,20286,111438,11129,34825,34884,15261,11684,28121,37430,11401,28903,38850,38611,21451,18127,25537,40729,47751,20437,23473,23751,24088,34494,18032,11228,49343,31190,23913,6860,14993,91357,30825,15077,24927,23799,13034,2161,32096,62852,31616,30717,1852,39025,32735,91649,32030,20547,29677,29874,82156,22622,9076,41174,674,33631,29531,3925,2035,11593,31670,74318,16146,20470,11266,26568,29199,30629,31133,8811,42705,28263,37616,16896,10884,51580,6827,41430,2365,24429,24047,55062,84491,21594,23405,100422,18595,25582,15746,45351,77439,45229,5835,31784,52821,11819,23258,9320,98581,10532,14939,2111,32175,46087,10991,39832,898,11247,9559,33829,55639,47833,17057,806,5373,9712,73657,15006,27862,3121,526,64499,3791,33439,41810,36777,38396,30783,77477,91639,55899,13054,20177,20169,28617,7085,36162,100589,20510,58762,14341,102622,31768,44761,21473,17324,1473,14784,28757,51408,109635,41918,85030,19980,13615,25020,20451,105788,32606,2388,16294,29782,23416,48275,44380,34697,17426,116170,14342,42148,4147,53862,85711,21230,105295,4329,16536,25308,81749,33750,12635,18680,80124,24805,86707,3672,76428,23718,26096,33327,42227,17822,52890,77968,70819,17582,42869,51946,40206,9280,112597,41568,22497,17281,107926,80107,20255,30930,41516,23525,41244,47497,25017,101864,26770,40450,49499,45283,56575,308,27881,25243,106374,37056,34551,11043,9620,8217,102833,26930,41184,28344,35071,61691,26400,16321,28059,106997,53996,91790,21834,28178,8208,15558,108014,36880,9252,16995,15220,8891,693,2047,7632,21627,92128,40110,23578,2592,43512,35634,47607,98696,27114,75558,84899,88984,1921,34429,14737,23213,39280,20056,54545,16453,4501,28679,1954,15963,34545,85741,30660,15413,39992,28656,9616,15623,31003,21751,36011,16996,64670,44071,22169,9395,15244,93210,35756,98370,98463,109896,2183,28457,90741,90392,46678,47181,26411,106274,47130,33819,8872,8380,145,43211,17096,85365,90976,51750,99060,43271,34111,17141,14369,49892,8303,663,20130,53880,16275,3935,9683,26980,7022,24959,15407,25628,23300,77749,1519,35009,50934,50880,67486,27011,1433,15921,77141,1269,32523,17135,7325,1290,1854,33946,38187,93279,109992,45062,16132,31933,92404,8703,43815,24804,37469,46452,55051,44845,27096,59024,9748,49692,2771,31082,92753,22619,98904,16227,24038,84227,14957,35968,35834,37694,32003,3673,25435,7256,38047,29781,25325,39063,28899,19318,38296,11525,44462,37169,118517,17583,7672,49835,66324,79290,38789,115632,12771,12118,33414,61111,32257,118393,86706,25577,47577,2100,30225,49609,29156,8911,59741,36563,67664,37265,36220,15598,26567,32145,28012,87821,15739,50904,28179,45315,16879,91164,40283,74741,26821,60508,15297,49414,20252,14806,15429,34146,16797,36578,15038,23325,885,29457,91179,40563,32653,116146,42639,3416,28657,3801,34431,19435,22114,20722,73295,50972,9183,41139,106506,9621,105773,33345,36018,118116,22838,39111,37216,2540,93192,36877,42889,36164,9144,762,25992,20516,23045,13957,31551,73905,35094,33764,35212,47467,19188,41559,29960,46384,24197,46191,50244,44548,46139,12325,35885,23021,27859,15042,60047,55805,35793,73774,22585,29830,21539,86752,1602,23313,35675,23727,119967,2884,12140,23838,35375,81371,22382,14579,49329,34453,104555,1425,7527,94310,17512,8102,10742,32359,66849,43897,43373,1909,71816,85368,20463,11666,39469,108991,99430,21715,26186,99061,43278,44889,26756,86897,22884,2275,1013,7191,22967,91970,58368,26308,34600,20356,89172,40468,32093,37293,17180,31949,15752,22686,61716,93741,49808,25261,61367,15097,74062,30318,45510,50964,33835,48822,10482,71427,14132,2093,10284,34285,28762,56707,37035,102874,40391,2088,36568,81451,48846,14529,92343,41933,40742,111019,82354,78720,36154,55043,23750,8700,23910,111760,1851,47448,1211,13111,8632,31129,25946,34957,35640,23909,114886,22063,12539,15074,20664,28247,23460,38705,2776,81999,23697,34075,6989,74623,51266,31566,21854,50594,103648,13419,74265,30788,35718,24801,567,117628,16893,42163,38816,60667,40257,31613,32239,42037,45051,28122,28902,12673,34826,27921,38137,32764,21991,60945,8191,55325,14143,25520,104578,33669,37909,11320,51436,79922,28943,36033,25229,84584,48349,41185,30169,38680,110617,45535,111121,38018,32590,37030,21692,33576,61483,83234,81036,66207,77868,41427,86541,31931,43746,30748,29297,79059,44585,6800,94387,12968,47429,2788,15618,777,58161,16147,12262,28916,16200,112267,29343,24715,2761,37869,36433,9162,23848,13685,39103,106807,32829,38874,103067,5061,46339,13658,37942,43586,9701,42708,17048,26813,68829,84493,98570,53421,86059,32967,43118,22456,43168,30839,10994,91719,77495,50998,12195,3085,11586,105695,102480,36069,17477,44073,76577,41593,23035,76141,15005,14866,76615,13622,33681,33294,19393,23291,47090,9798,30230,1670,34726,98838,8024,39702,698,15580,34954,47701,30277,27035,89184,88957,42824,31109,43972,41371,93721,87086,75135,41391,6490,15472,75522,42516,2255,43342,29429,32547,4337,77044,15300,34338,87298,913,47965,99424,91110,42641,75646,52602,13638,15831,14429,25941,42571,21825,52196,37636,108238,68295,28484,42553,46012,32700,31854,119323,10478,18727,2050,36783,61340,23206,99376,30563,77299,39674,8967,42374,7396,6813,102015,32646,45909,29041,36352,35686,21837,59715,36277,2381,38700,17590,35108,38996,16670,28118,14463,36758,105752,16992,9675,47810,102733,41861,34009,2048,5297,25246,36397,841,647,102697,43061,30260,82665,30591,39995,10902,14133,40070,10540,11539,44178,101040,30553,38455,93524,7457,9138,28720,110695,101996,98139,76812,91457,32128,14921,34886,32200,79044,32820,2200,15766,32130,1109,114564,12148,12972,39461,3144,15133,39692,9390,93964,13370,45630,23210,14152,30244,20142,90212,1341,81248,7406,26189,15572,6873,34164,80293,19410,46615,36127,8357,26539,15807,38238,26862,52774,29790,23298,26432,21235,116613,91026,6913,22782,5196,16003,113234,13900,10762,1430,90934,22053,24956,24055,49102,6843,1462,9589,22142,105145,86432,17374,25598,43120,40270,17241,116983,24996,17771,38737,16433,14362,102887,7776,44127,32080,50895,82317,12130,43023,28252,24381,2572,78431,38844,18165,14719,20647,118593,30331,6566,47430,16389,86883,109991,91582,103327,82578,100906,15666,3188,49406,15487,42492,15989,28823,21735,82900,28931,90846,13530,36507,21949,12648,43729,48190,35139,27988,15795,60922,12751,19323,88404,100878,81893,42651,30994,2401,63309,18774,34463,4484,87351,78624,20154,15901,16068,1178,335,20074,41917,80000,7002,1288,11381,85173,77519,1240,32058,13508,19270,41049,52409,87231,23103,33926,23812,14740,18139,30615,22182,8807,27181,89522,40605,55415,46153,44408,28079,19201,34766,57001,78851,57286,11162,61354,4410,31155,25838,16084,2685,53878,49269,73551,13809,94872,52022,1807,35621,74699,82026,79060,31445,94622,15725,83322,13465,29879,37496,2616,26553,5159,46113,38174,102055,48170,22733,36846,809,83336,2728,1186,55061,44219,79991,103463,20234,2534,32339,24755,45491,58493,51089,76368,84879,3179,24743,17170,51347,60191,14894,43183,21893,32496,2038,60611,42083,87834,33963,30357,39038,45925,57959,28574,2114,37366,2441,44802,28603,37994,39882,84204,31806,55086,26995,33221,45593,89519,37372,30052,91959,44856,41051,117700,51091,47484,29711,12241,103778,77073,84031,16370,41365,52126,11246,18744,35247,55590,52635,36239,8937,48229,51315,42791,17136,100002,79712,32037,27164,42485,40250,21764,104608,28637,38749,37736,82940,30354,14217,1300,45555,11995,38459,32756,2872,25774,15067,81074,38011,33616,68566,21736,68485,45309,33747,82314,1068,31171,28309,38243,19472,3189,13708,60947,6851,8449,33132,14002,43885,34536,23734,93853,2006,15486,15422,115646,80700,57944,28826,25718,33715,15432,44987,15612,65192,14350,37919,49913,13636,15513,32518,16169,88514,51463,108479,29164,33144,51668,117459,23104,62222,8466,70578,38830,71787,82585,80828,67799,26896,47115,87781,51523,20346,94481,7236,29134,34288,23341,21683,16562,76376,18034,52437,25124,30618,51829,103059,25106,31633,23175,91923,16579,35605,47121,709,2577,2410,119375,86695,39439,23677,24846,52040,98574,21207,21446,30567,37679,22734,20658,52450,35531,50777,31828,26222,42650,33268,42699,43576,37483,37457,24378,91710,45701,11065,37191,18068,23438,108924,12719,37573,77096,47513,35320,28959,44312,54242,40945,17424,8548,49736,22794,105763,24405,41720,27993,43428,10698,53967,55147,36980,100124,46447,14317,45857,31803,41280,52051,7344,28600,20246,93351,1977,26965,6982,31738,16901,46390,13763,81008,17828,34806,3154,9781,248,17326,98201,4311,14042,31122,37590,24817,15454,79554,89162,41481,111730,77675,28619,34206,94096,35190,23554,39778,31880,38130,42366,55593,41368,26662,43308,43626,16233,22999,44530,42019,16505,9026,29095,43184,57284,111727,38489,9824,28585,26190,41052,63647,49159,44512,28029,36351,63867,11656,84394,27085,16801,45230,18100,9220,35499,14793,9194,85954,23890,35026,42790,76423,35077,26011,32052,80569,92917,42949,50641,43484,36283,49464,15471,43965,24899,1362,76229,65216,47640,22930,16267,85160,92781,8757,27008,7551,28792,44183,47964,54891,75202,19936,11033,41690,30325,25916,39008,13540,18090,37922,34757,11494,28112,21844,35207,36396,30940,8206,36939,74653,26390,23502,17293,48942,105685,41425,40510,100824,42213,102413,84633,16810,102294,22019,49602,8581,47464,100022,1775,25027,33035,25148,14088,99783,36008,35267,35571,874,45994,261,88164,71577,20852,30173,24444,34008,15505,63149,39939,28066,17445,7985,45163,31146,8539,7153,37782,22678,23167,21723,28172,13925,35289,23362,23059,70454,31430,38919,30562,7724,102089,12748,1783,25509,39448,51949,3226,30590,32408,32310,45899,91064,41549,28102,47898,65434,13655,29150,88931,100184,32936,15084,93737,11622,14097,37980,17014,1374,25134,11904,83379,61479,10576,35389,17252,24861,18666,26375,86425,25037,15974,13677,10916,8934,28404,1042,12540,28229,17719,36856,26908,64816,839,112156,17776,102127,93266,46176,47661,115081,11409,20265,108677,118728,21727,23895,4490,31680,37225,9135,22689,35311,30725,2639,741,43960,41894,31658,20563,25150,26440,26165,119577,33724,45778,21795,103575,13785,20053,10379,8752,33917,28718,47125,39578,93571,31664,30739,656,19902,24733,37288,22441,38790,90391,2700,7784,11379,30914,8194,46097,94476,47817,107100,33430,113005,105388,22478,32460,27064,38122,78538,50176,1674,16890,29068,118864,25675,53447,43642,28367,32933,29463,73200,41262,33054,16187,1731,46507,40810,938,35613,25913,1950,38416,41420,34579,8160,7422,100273,33022,29443,30180,113093,76359,37795,9084,40036,20032,23906,79218,41524,78261,114510,9629,81740,94654,45894,9771,11655,50858,43988,7606,59105,13606,41846,50119,29237,28183,1377,79889,88884,16051,12724,72187,21984,80506,25516,43675,37856,83215,36768,98288,1073,15653,20239,14911,85131,7727,33156,98919,41251,16911,37510,35765,28911,33778,44507,37754,26000,18081,32921,24691,94233,42407,15524,40599,19899,50135,15400,40964,88934,22462,2327,20417,24637,101384,15214,36968,45169,76489,26317,24751,40757,17716,40463,30105,42805,64811,16806,26641,8495,1847,102681,79457,43219,10573,11313,29769,17255,14399,42478,18663,30035,49477,56945,36048,86671,38906,117608,52343,13744,32410,40954,105620,66716,14926,43918,25173,43581,72195,17263,34400,44242,20113,23711,36929,49301,25092,2014,90339,16348,35449,29185,8547,20224,84580,42394,30840,784,68561,117118,27869,109641,15736,51302,33187,1898,13965,51330,115045,20172,114891,34948,25822,14155,18588,44842,88465,80622,23607,37049,8196,42533,36700,4293,63367,37896,4781,37476,11785,45550,36571,75144,36751,92932,14803,21861,20160,29161,6468,90853,28796,51149,76407,51520,42798,19912,2223,30218,32862,21800,2958,89860,76243,82955,117781,3217,24281,81303,16790,35907,41221,47137,1487,36078,26523,81802,110337,26808,35360,11059,9481,79228,75214,9267,30736,35913,32045,107071,8739,23178,68719,1121,24793,26421,42685,15593,31023,42451,37268,11428,17529,49321,44850,102924,44555,11354,47504,24262,59190,51363,32455,42445,2300,76571,77668,92004,14284,20011,1114,82854,110313,13997,14101,54825,92685,34178,28936,36151,37592,82689,2942,55868,72775,35991,45447,22894,36269,107871,43413,28109,74248,31606,116180,8367,22799,91529,11765,42089,91860,39820,33348,43129,13432,18079,8489,21547,34985,16904,36201,25463,1449,2105,21570,88839,32486,50291,25701,35435,21597,37444,75254,17574,118896,107688,82673,35063,14035,103624,117182,23871,79996,11240,43097,35241,31883,92266,20241,22806,71256,25112,32637,63644,45070,35899,23162,704,37066,36565,84890,88947,92010,87913,10583,28591,14965,90214,61711,2982,39557,34162,29140,47394,44887,64743,92464,20165,98263,8515,35332,86455,88124,32715,25671,11218,293,88651,41867,42536,32731,34380,21694,26056,25216,51534,54723,74895,48250,16558,20559,2398,92985,24195,27098,34952,54620,113605,31075,52813,45815,47707,49764,13477,617,9066,22167,120439,51320,55669,8203,9040,34752,50821,12534,31040,77880,11696,6613,15327,2347,30497,39277,50540,9213,32566,1862,100364,40087,39690,9677,34622,21966,14833,86999,42372,80570,14085,27926,14959,37438,19521,30442,32236,45747,30541,78696,82704,79054,44582,22388,49185,29796,26434,46628,30242,37441,114427,34798,56192,26456,19244,10429,25951,34391,31089,33766,29937,43781,17518,39807,32677,2764,12384,16408,41753,16799,17312,92009,1407,40014,51283,2542,27003,65613,28106,83197,31790,23307,24456,38598,36875,23518,42419,10462,1332,13891,76526,16239,39035,12611,36859,34017,28830,21676,1035,35988,1139,116595,45512,13680,34649,23294,42849,113916,23066,14849,45884,23823,74052,34877,4092,52008,13947,51445,7198,93653,2332,2125,38322,22107,728,41537,1036,44451,37132,28385,39769,28018,23800,32006,17199,52155,10317,20035,15029,16060,2065,18137,38857,41935,75244,37164,40556,30613,66858,39141,90902,29273,73263,3315,45360,28829,8525,2814,81909,54219,15987,73464,75238,40712,41504,22431,13500,13934,20695,31758,38291,31868,23433,105907,49411,46809,24186,19905,15294,47355,33560,3722,21947,99486,26635,47411,89524,20000,42593,48180,40896,13538,34701,92988,93581,53229,10253,5747,11697,120104,363,34262,29666,41311,76419,21227,67734,33003,71772,75033,47490,27978,47814,22170,62543,88423,107485,23842,120272,31503,33769,8438,46220,1944,13124,52513,32337,36874,41491,24694,3053,40077,76240,28331,34689,32203,11719,7460,31183,27183,46846,17456,15637,41752,55649,28605,31205,34294,37996,1830,26551,17799,31991,75419,19559,34342,14857,11977,17780,36167,41584,47882,22890,16221,119366,49431,15707,44863,35020,51466,18094,2260,30032,9038,7524,13407,43378,19987,32252,84003,26143,25097,71807,99958,26323,15804,44884,22868,65832,26918,94679,42643,50886,5338,40664,13348,22835,22228,49457,47876,8687,22387,40754,47277,65907,22941,78695,52535,89403,45154,50624,88759,11563,559,5522,37340,12292,41147,6872,23596,84567,21678,91425,117557,25176,10337,21521,77615,120139,15913,22329,51476,17393,23353,67514,34167,1143,8598,43858,52581,51303,4047,82589,17166,34468,51385,76219,13757,25714,43159,4832,90897,23333,33091,47104,33756,64280,66032,74705,93885,16813,7080,29958,1491,256,18670,38872,2206,31757,48043,10988,13515,2167,40491,22430,1636,17091,17540,45250,38181,49759,49820,30211,11608,649,32474,40377,16920,47354,52702,21870,22948,31177,2536,11931,105794,31107,22694,24118,23774,117227,31344,49631,32009,52287,99308,37911,18046,33925,7649,13715,88481,43354,76161,7620,87998,25305,30593,18739,17320,37626,21477,30186,50559,33844,71466,3054,20038,25012,14612,24971,46629,102107,44412,35820,22188,107069,11866,8958,71079,42723,29787,27975,20367,23560,8992,86930,26041,48183,20482,75133,18781,23738,85074,120101,42070,78126,31141,8156,20425,26736,26550,39536,22942,31541,32425,14730,2912,47873,30880,36906,17021,42979,101541,73058,110793,19908,106712,16448,45762,6922,31506,31036,2054,118924,55646,46775,94658,15135,14299,14580,54508,34891,813,30018,82318,26634,4727,54628,50212,39676,91883,51107,49849,11974,32939,13650,35338,93926,17798,32696,23261,117360,2847,86469,93499,103343,59127,108008,113694,1060,59675,24383,17453,34635,106251,488,19532,25175,98088,12692,5525,72429,17349,2815,76189,22249,2531,13121,48308,24877,22393,37791,16407,52115,10336,92461,41319,20809,41378,50564,15914,32204,26915,15052,22836,57988,118580,81259,1516,49022,36447,1923,47823,36762,92811,39305,54884,14048,104207,31980,40053,45167,98657,759,38526,25380,24834,39688,34828,38562,26701,36687,47142,15264,12293,44395,38546,49830,11876,1184,110534,31072,6727,36805,55056,50538,8021,24131,2250,97458,37953,31097,118206,29119,55339,36063,38555,43887,22929,93194,52824,33562,32165,30272,35901,88205,14856,18636,916,23285,40409,36035,17593,11506,81639,102599,28250,1019,1007,7000,54137,25950,36274,29020,23219,50868,14848,21695,33573,50788,108852,52084,51072,45676,45840 "',0,0,0)
where display_term NOT LIKE 'nn%'

Msg 7630, Level 15, State 2, Line 1

Syntax error near '"' in the full-text search condition '"13557,34782,17331,11156,24333,34839,11928,22900,22179,40749,8507,51198,69060,40488,10843, ... 34545...

Reference

sys.dm_fts_parser (Transact-SQL)

Conclusion

Hope this tutorial has explained the benefits of using this function and also its limitation. We can now use it wisely and judiciously for easing our problem when we need to split a string into an array and get a list of each word separately.

Thanks for reading