T-sql numbers issue

Hi,

My table filed called ID_number ,see below
ID_Number
00001
00002
00035
00925
00088

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


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


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






RETURN @Value
END

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
ukerandiAsked:
Who is Participating?
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
Here it is (not tested):

function GetSum(@s as varchar(5)) returns int32 as
BEGIN
  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)
  END;

  return @sum;
END;

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;
0
 
Neil RussellTechnical Development LeadCommented:
How many rows in the table?
Is this a one off exercise or ongoing with new rows?
0
 
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.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi ukerandi,

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


Kent
0
 
Neil RussellTechnical Development LeadCommented:
Has some serious reading to do if it is....
0
All Courses

From novice to tech pro — start learning today.