there is always something to develop

How to calculate total of digits in a varchar field

To calculate the total of digits in varchar field, you can do it in two different ways. First one is to calculate it using matrix processing (without loops). As a first step we create temporary table that contains at least as many records as the longest varchar field.

Subsequently , simply select exactly as many records from as the length of processed varchar getting in each record a character (digit) present at that particular position. Off course we exclude not numeric characters at this moment. Next we sum up the result.

declare @table table( [Index] integer default 0 ) INSERT INTO @table([Index]) select cast(1 as int) as [Index] union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0 declare @cText as varchar(32) set @cText = '1235a54dgh5557890'; select sum( cast(substring(@cText,(t.[Index]*10+t2.[Index]),1) as int) ) from @table t inner join @table t2 on (1=1) where (t.[Index]*10+t2.[Index])<=Len(@cText) and IsNumeric(substring(@cText,t.[Index]*10+t2.[Index],1))=1 and (t.[Index]*10+t2.[Index])>0

In the result you get 59 as a total of all digits

The next solution requires using loop and it might be slower a little bit. However the length of the varchar will not be too long so I think it does not make too much difference.

declare @cText as varchar(32) set @cText = '1235a54dgh5557890'; declare @Index integer; declare @Total integer; set @Index=1; set @Total=0; while @Index<=len(@cText) begin if IsNumeric(substring(@cText,@Index,1))=1 set @Total=@Total+cast(substring(@cText,@Index,1) as integer) set @Index=@Index+1 end select @Total

**See also**

Comments

- [List of topics]
- Outsourcing IT
- References
- Leave message
- Guest book
- Circulum Vitae
- Download CV (PDF format)

"The definition of insanity is doing the same thing over and over and expecting a different result"

You are **4082** reader

since 13th August 2009

Was this document helpful?

Yes | 5.9% |
1 |

No | 94.1% |
16 |

Your vote