T-sql numbers issue

Posted on 2014-11-22
Medium Priority
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.
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

LVL 46

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 58

Accepted Solution

HainKurt earned 2000 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

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

764 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