need to get a sum of numbers in a number

[ndp]

Hi,

I have a number x = 4653. I want to keep adding the individual numbers from x until I get the single digit number. Like x = 4+6+5+3 = 18 and then 1+8 = 9 which is the final number I want. The x might vary in length. It could be 1 or 25 or 98759937. Is there any function in Transact sql to do that? Any suggestion is welcome.

Thanks in Advance
ndp

 

[JamesLean]

There's no built-in function that will do that. If you're using SQL 2000 then you could write your own user-function to do it.

--James

 

[Discord]

i don't know of a built-in function that will do this. here is a function that will do it though

Create function dbo.AddDigits
(
@intNumber int
)
Returns int
AS
Begin

declare @strNumber varchar(20), @intReturnValue int

Select @strNumber = Cast(@intNumber as varchar), @intReturnValue = 0

while (@strNumber <> '')
begin
Set @intReturnValue = @intReturnValue + Cast(Left(@strNumber, 1) as tinyint)
Set @strNumber = SubString(@strNumber, 2, len(@strNumber))
end
if (@intReturnValue > 9)
Select @intReturnValue = dbo.AddDigits (@intReturnValue)
return @intReturnValue
End
go

and call it like so
select dbo.AddDigits (12345)

 

[ndp]

Thanks for the help!

I did similar to what you suggested and it works! I was going to do that way only but thought there might be some built in function available which I can use to minimize steps. But, this works Great! Thank you for your time.

--ndp