Solved

T-sql numbers issue

Posted on 2014-11-22
6
129 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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. …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

772 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