Second solution, which is more efficient than the previous one as it doesn't use the XML concatenation. The code is somewhat self-explanatory.
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_STR VARCHAR(8000) = '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;
DECLARE @DELIMITER CHAR(1) = ',';
/* Inline Tally Table
Range 1-8000
*/
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)
) AS X(N))
, NUMS(N) AS (SELECT TOP(LEN(@SAMPLE_STR)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3)
/* Base information
W_POS = Position of the search word within the string
W_LEN = Length of the string
*/
,WORD_POS AS
(
SELECT
CHARINDEX(@SEARCH_WORD,@SAMPLE_STR,1) AS W_POS
,LEN(@SAMPLE_STR) AS W_LEN
)
/* Identify the position of all delimiters in the string.
LAG and LEAD to bring the desired range of
(@WORD_COUNT x 2) + 1
*/
,WORD_GROUPS AS
(
SELECT
NM.N
,WP.W_POS
,WP.W_LEN
,LAG(NM.N,@WORD_COUNT,0) OVER
(
ORDER BY NM.N
) AS START_POS
,LEAD(NM.N,@WORD_COUNT + 1,0) OVER
(
ORDER BY NM.N
) AS END_POS
FROM NUMS NM
CROSS APPLY WORD_POS WP
WHERE SUBSTRING(@SAMPLE_STR,NM.N,1) = @DELIMITER
)
/* Find the group where the search word is closest to the
delimiter and calculate the length of the extraction
*/
,RANKED_GROUPS AS
(
SELECT
DENSE_RANK() OVER
(
ORDER BY ABS(WG.W_POS - WG.N)
) RG_DRNK
,WG.START_POS
,CASE
WHEN WG.END_POS = 0 THEN WG.W_LEN - WG.START_POS
ELSE (WG.END_POS - WG.START_POS) - 1
END AS XLEN
,WG.W_POS
FROM WORD_GROUPS WG
)
/* Return NULL if the search word is not within the string
else the right group of words.
*/
SELECT
CASE
WHEN RG.W_POS = 0 THEN NULL
ELSE SUBSTRING(@SAMPLE_STR,RG.START_POS + 1,RG.XLEN)
END AS OUTPUT_STR
FROM RANKED_GROUPS RG
WHERE RG.RG_DRNK = 1;
Results
OUTPUT_STR
--------------------------------------------------------------------------
imagination,love,world,lovely,confusion,introspection,on work and working