Solved

T-sql numbers issue

Posted on 2014-11-22
6
125 Views
Last Modified: 2014-11-23
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
0
Comment
Question by:ukerandi
  • 4
6 Comments
 
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?
0
 
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.
0
 
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.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 45

Expert Comment

by:Kdo
ID: 40460120
Hi ukerandi,

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


Kent
0
 
LVL 37

Expert Comment

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

Accepted Solution

by:
HainKurt earned 500 total points
ID: 40460292
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now