Quick solution which uses dbo.DelimitedSplit8K[/url]

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @SAMPLE_STR VARCHAR(500) = 'adventure,devotion,for children,happiness,imagination,love,world,lovely,confusion,introspection,on work and working,on writing and words,';

DECLARE @SEARCH_WORD VARCHAR(50) = 'lovely';

DECLARE @WORD_COUNT INT = 3;

;WITH WORD_LIST AS

(

SELECT

WL.ItemNumber

,WL.Item

,MAX(CASE

WHEN WL.Item = @SEARCH_WORD THEN WL.ItemNumber

ELSE 0

END) OVER

(

PARTITION BY (SELECT NULL)

) AS WL_FLAG

FROM dbo.DelimitedSplit8K(@SAMPLE_STR,',') AS WL

)

SELECT

STUFF( (SELECT

',' + W.Item

FROM WORD_LIST W

WHERE W.ItemNumber BETWEEN W.WL_FLAG - @WORD_COUNT AND W.WL_FLAG + @WORD_COUNT

FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(500)')

,1,1,'') AS OUTPUT_STR;

Results

OUTPUT_STR

--------------------------------------------------------------------------

imagination,love,world,lovely,confusion,introspection,on work and working