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
If you like this article, subscribe to our 
RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.