Parse numbers from string, then total them
I have a column which contains a poorly formed string. An example would be: 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 What I need to do is find all the numbers proceeded by "TOTAL:" then get a total of those numbers. It should ignore all other numbers. So, for this example, my query should return 306 (ignoring the number following SCORE) I'm not sure how to even proceed with this one.
Without going into detail for reasons why, I can't do this by creating a new UDF. I know that complicates it somewhat.
Best Answer
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;
