## 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
```

14th July 2021

dapoxetine viagra combo

zivacle

18th August 2021

JosephObeva

20th August 2021

10mg cialis cost cialis 20mg tablets cialis 50mg price cialis costco price cialis black market cialis 20mg usa

Michaelreoto

31st August 2021

uribete

8th September 2021

empaxyJax

10th September 2021

ceclor lipitor anafranil abhigra imovane addyi

Harveybot

12th September 2021

stromectol no prescription

wabrhyday

19th September 2021

AmyLoalf

19th September 2021

JaneLoalf

19th September 2021

JasonLoalf

19th September 2021

AnnaLoalf

20th September 2021

Is Amoxicillin The Same As Penicilin

plaquenil toxicity symptoms