T-SQL how to SUM a text data?

Question

I got a field value 0+6+6+6+0+0+0 data type is varchar. how can I get sum of it.

I have tried to convert to int but I got error.

select cast('0+6+6+6+0+0+0' as int)

I want output as 18.

Answer 1

Assuming you want to perform the calculation on every record in your table.

Example

Declare @YOurTable table (ToEval varchar(50))
Insert Into @YOurTable values
 ('0+6+6+6+0+0+0')
,('5+6+25')

Select A.*
      ,B.*
 From  @YOurTable A
 Cross Apply (  
                Select Value = sum(Value)
                 From  (
                        Select  Value = B.i.value('(./text())[1]', 'int')
                        From  (Select x = Cast('<x>' + replace(ToEval,'+','</x><x>')+'</x>' as xml)) A 
                        Cross Apply x.nodes('x') AS B(i)
                       ) B1
             ) B

Returns

ToEval          Value
0+6+6+6+0+0+0   18
5+6+25          36