question

This isn't very pretty code, but it will do the trick, I Think. It starts off with two CTEs which will create a number series, then it will find all occurences of the word TOTAL:, and get the start and end position of what follows after the word TOTAL: and finally select substrings from the string, with start and end positions from the preceding CTEs. I use REPLACE of TOTAL: followed by a blank with TOTAL: without the blank (because they were written differently in the string. declare @s varchar(max)='stuff more stuff other stuff TOTAL: 22 even more stuff things TOTAL:35 here is some SCORE: 53 more and other things TOTAL:249 more stuff i don''t need here'; with cte as ( SELECT 0 as n UNION ALL SELECT n+1 FROM cte WHERE n<9 ),cte2 as ( SELECT ROW_NUMBER() OVER(ORDER BY c1.n) as n FROM cte as c1 CROSS JOIN cte as c2 CROSS JOIN cte as c3 ),cte3 as ( SELECT CHARINDEX('TOTAL:',REPLACE(@s,'TOTAL: ','TOTAL:') + ' ',n) + LEN('TOTAL:') as startpos FROM cte2 WHERE CHARINDEX('TOTAL:',REPLACE(@s,'TOTAL: ','TOTAL:') + ' ',n)=n ),CTE4 as ( SELECT startpos,CHARINDEX(' ',REPLACE(@s,'TOTAL: ','TOTAL:') + ' ',startpos) as endpos FROM cte3 )select SUBSTRING(REPLACE(@s,'TOTAL: ','TOTAL:'),startpos,endpos-startpos) FROM CTE4;