Convert column from string of numbers to a subtotal on select statment This probably very basic but I have struggled to figure out. I have looked around but couldn't find any similar problem. I am using SQL Server 2008 R2

I have a view:

ID  |           VALUE
1   | 151;181;179;185;160;187
2   | 185;160
3   | 151;181;179;185;150
4   | 185;160;187
5   | 187


I want a select statement to produce this results:

ID  |           VALUE
1   |           1043
2   |           345
3   |           846
4   |           532
5   |           187
This is definitely not a good data model.

If you are stuck with it you could use

SELECT ID, 
       X.value('sum(/x/text())', 'FLOAT') as Value    
FROM YourView
CROSS APPLY (VALUES(CAST('&lt;x&gt;' + REPLACE(VALUE, ';', '&lt;/x&gt;&lt;x&gt;') + '&lt;/x&gt;' AS xml))) V(X)


Online Demo