T-sql numbers issue

Posted on 2014-11-22
Last Modified: 2014-11-23

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
Question by:ukerandi
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
LVL 37

Expert Comment

by:Neil Russell
ID: 40460070
How many rows in the table?
Is this a one off exercise or ongoing with new rows?
LVL 37

Expert Comment

by:Neil Russell
ID: 40460077
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.
LVL 37

Expert Comment

by:Neil Russell
ID: 40460094
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.
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

LVL 45

Expert Comment

by:Kent Olsen
ID: 40460120
Hi ukerandi,

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

LVL 37

Expert Comment

by:Neil Russell
ID: 40460123
Has some serious reading to do if it is....
LVL 53

Accepted Solution

Huseyin KAHRAMAN earned 500 total points
ID: 40460292
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;

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question