MS SQL Tips & Tricks

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


Learn more tricks

Add Comment

Comments