T-sql numbers issue


My table filed called ID_number ,see below

I Wrote Following Function im not sure its good way
ALTER FUNCTION AddResults(@Num1 bigint)
RETURNS bigint
DECLARE @Value bigint
DECLARE @LengthofValue int

SET @LengthofValue=LEN(@Num1)
IF(@LengthofValue=1 OR @LengthofValue=0)
SET @Value=@Num1

SET @Value=LEFT(@Num1,1)+ CAST(SUBSTRING(CAST(@Num1 as nvarchar(10)),2,1) as bigint)


Open in new window

this half way of function (half finish)

What is need to do is
i need add another column and expected results is Add ID Number values and insert into another filed
For example 0035 expected results is 3+5=8
ID_Number    Expected Results
00001             1
00002              2
00035              8
00925             16
01188              18
99999               45

Can some give me any idea how to do that
LVL 10
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neil RussellTechnical Development LeadCommented:
How many rows in the table?
Is this a one off exercise or ongoing with new rows?
Neil RussellTechnical Development LeadCommented:
Anyway, you mentioned C# in the topics so here is a quick snippit that will do the conversion you want in C#.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace EEDemo952
    class Program
        static void Main(string[] args)
            int ID_Number = 978;
            int Expected = 0;
            string strID;

            strID = ID_Number.ToString();
            for (int iLoop = strID.Length; iLoop > 0; iLoop--)
                Expected += Convert.ToInt16(strID.Substring(iLoop-1, 1));

            Console.Write( Expected);

Open in new window

You would just need to iterate through the table and update with the value of Expected for each row.
Neil RussellTechnical Development LeadCommented:
You could of course add a new CLR UDF to your sql instance that defines a new function called StrTOInt that implents that small snippet of code. Accepts a string and returns an int;

Of course we dont know your level so creating UDF's in code may be out of scope for this but is a thought.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Kent OlsenData Warehouse Architect / DBACommented:
Hi ukerandi,

This is hardly a normal thing to do in SQL.  Is this homework?

Neil RussellTechnical Development LeadCommented:
Has some serious reading to do if it is....
HainKurtSr. System AnalystCommented:
Here it is (not tested):

function GetSum(@s as varchar(5)) returns int32 as
  DECLARE @sum as int32 =0;
  DECLARE @i as int32 = 1;

  WHILE @i <= len(@s) BEGIN
    SET @sum = @sum + cast(substring(s, i, 1) as int32)

  return @sum;

Open in new window

after this, it is easy:

update MyTable set OtherColumn = GetSum(ID_Number);

or just select

select ID_Number, GetSum(ID_Number) as SumOfNumbers
from MyTable
order by ID_Number;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.