Avatar of ukerandi
ukerandi
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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
C#Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
HainKurt

8/22/2022 - Mon
Neil Russell

How many rows in the table?
Is this a one off exercise or ongoing with new rows?
Neil Russell

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 Russell

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Kent Olsen

Hi ukerandi,

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


Kent
Neil Russell

Has some serious reading to do if it is....
ASKER CERTIFIED SOLUTION
HainKurt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question