question

siugoalie78 avatar image
siugoalie78 asked

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.
sql-server-2008-r2string
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Without going into detail for reasons why, I can't do this by creating a new UDF. I know that complicates it somewhat.
0 Likes 0 ·

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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;
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Works like a champ! Thanks so much
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.