Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Add, Sub, Mul, Div With Big Numbers

Author  Topic 

Hunglech
Starting Member

16 Posts
Posted - 2005-08-04 : 04:40:25
You can Add, Sub, Mul, Div With Big Numbers
Ex:
SELECT dbo.xDiv('127435627567236572365726354625763275623756237657236572365273', '68436783476843674387683476843434367346', 10)

The output is:
1862092592507007503619.7638832121

CREATE FUNCTION xDiv(@xText VARCHAR(8000), @yText VARCHAR(8000), @n INT)
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @j INT, @sx VARCHAR(8000), @sy VARCHAR(8000), @strRet VARCHAR(8000)
SELECT @i = dbo.xSign(@xText), @j = dbo.xSign(@yText), @sx = dbo.xAbs(@xText), @sy = dbo.xAbs(@yText)
SELECT @sx = dbo.xCutFraction(@sx), @sy = dbo.xCutFraction(@sy)

IF @i = @j
SELECT @strRet = dbo.DivFraction(@sx, @sy, @n)
ELSE
SELECT @strRet = '-' + dbo.DivFraction(@sx, @sy, @n)
RETURN @strRet
END


CREATE FUNCTION xMul(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @j INT, @sx VARCHAR(8000), @sy VARCHAR(8000), @strRet VARCHAR(8000)
SELECT @i = dbo.xSign(@xText), @j = dbo.xSign(@yText), @sx = dbo.xAbs(@xText), @sy = dbo.xAbs(@yText)
SELECT @sx = dbo.xCutFraction(@sx), @sy = dbo.xCutFraction(@sy)

IF @i = @j
SELECT @strRet = dbo.MulFraction(@sx, @sy)
ELSE
SELECT @strRet = '-' + dbo.MulFraction(@sx, @sy)
RETURN @strRet
END

CREATE FUNCTION xSub(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @j INT, @sy VARCHAR(8000), @strRet VARCHAR(8000)
SELECT @j = dbo.xSign(@yText), @sy = dbo.xAbs(@yText)
IF @j = 1
SELECT @strRet= dbo.xAdd(@xText, '-' + @sy)
ELSE
SELECT @strRet= dbo.xAdd(@xText, @sy)

RETURN @strRet
END

CREATE FUNCTION xAdd(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @j INT, @sx VARCHAR(8000), @sy VARCHAR(8000), @strRet VARCHAR(8000)
SELECT @i = dbo.xSign(@xText), @j = dbo.xSign(@yText), @sx = dbo.xAbs(@xText), @sy = dbo.xAbs(@yText)
SELECT @sx = dbo.xCutFraction(@sx), @sy = dbo.xCutFraction(@sy)

IF @i = 1
BEGIN
IF @j = 1
SELECT @strRet = dbo.AddFraction(@sx, @sy)
ELSE
IF dbo.zCompare(@sx, @sy) <> -1
SELECT @strRet = dbo.SubFraction(@sx, @sy)
ELSE
SELECT @strRet = '-' + dbo.SubFraction(@sy, @sx)
END
ELSE
BEGIN
IF @j = 1
IF dbo.zCompare(@sy, @sx) <> -1
SELECT @strRet = dbo.SubFraction(@sy, @sx)
ELSE
SELECT @strRet = '-' + dbo.SubFraction(@sx, @sy)
ELSE
SELECT @strRet = '-' + dbo.AddFraction(@sx, @sy)
END
RETURN @strRet
END


CREATE FUNCTION xCompare(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS INT AS
BEGIN
DECLARE @i INT, @j INT, @sx VARCHAR(8000), @sy VARCHAR(8000), @iRet INT
SELECT @i = dbo.xSign(@xText), @j = dbo.xSign(@yText), @sx = dbo.xAbs(@xText), @sy = dbo.xAbs(@yText)
SELECT @sx = dbo.xCutFraction(@sx), @sy = dbo.xCutFraction(@sy)

IF @i = 1
BEGIN
IF @j = 1
SELECT @iRet = dbo.zCompare(@sx, @sy)
ELSE
IF @sx = '0' AND @sy = '0'
SELECT @iRet = 0
ELSE
SELECT @iRet = 1
END
ELSE
BEGIN
IF @j = 1
IF @sx = '0' AND @sy = '0'
SELECT @iRet = 0
ELSE
SELECT @iRet = -1
ELSE
SELECT @iRet = -1*dbo.zCompare(@sx, @sy)
END
RETURN @iRet
END


CREATE FUNCTION zCompare(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS INT AS
BEGIN
DECLARE @iRet INT
DECLARE @sI VARCHAR(8000), @xIText VARCHAR(8000), @xFText VARCHAR(8000), @yIText VARCHAR(8000), @yFText VARCHAR(8000), @l INT
SELECT @xIText = dbo.xInt(@xText), @yIText = dbo.xInt(@yText), @xFText = dbo.xFraction(@xText), @yFText = dbo.xFraction(@yText)
IF LEN(@xFText) > LEN(@yFText) SET @yFText = @yFText + REPLICATE('0', LEN(@xFText) - LEN(@yFText))
IF LEN(@xFText) < LEN(@yFText) SET @xFText = @xFText + REPLICATE('0', LEN(@yFText) - LEN(@xFText))
SELECT @iRet = dbo.Compare(@xIText + @xFText, @yIText + @yFText)
RETURN @iRet
END


CREATE FUNCTION xAbs(@xText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @strRet VARCHAR(8000)
SELECT @strRet = CASE WHEN dbo.xSign(@xText) = -1 THEN RIGHT(@xText, LEN(@xText) - 1) ELSE @xText END
RETURN @strRet
END

CREATE FUNCTION xSign(@xText VARCHAR(8000))
RETURNS INT AS
BEGIN
DECLARE @iRet INT
SELECT @iRet = CASE WHEN LEFT(@xText, 1) = '-' THEN -1 ELSE 1 END
RETURN @iRet
END

CREATE FUNCTION DivFraction(@xText VARCHAR(8000), @yText VARCHAR(8000), @n INT)
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @sI VARCHAR(8000), @sF VARCHAR(8000), @xIText VARCHAR(8000), @xFText VARCHAR(8000), @yIText VARCHAR(8000), @yFText VARCHAR(8000), @x INT
SET @x = @n + 1
IF @x < LEN(@yText) + 1 SET @x = LEN(@yText) + 1
SELECT @xIText = dbo.xInt(@xText), @yIText = dbo.xInt(@yText), @xFText = dbo.xFraction(@xText), @yFText = dbo.xFraction(@yText)
IF LEN(@xFText) > LEN(@yFText) SET @yFText = @yFText + REPLICATE('0', LEN(@xFText) - LEN(@yFText))
IF LEN(@xFText) < LEN(@yFText) SET @xFText = @xFText + REPLICATE('0', LEN(@yFText) - LEN(@xFText))
SELECT @sI = dbo.DivInt(@xIText + @xFText + REPLICATE('0', @x), @yIText + @yFText)
WHILE LEN(@sI) <= @x SET @sI = '0' + @sI
SET @sI = LEFT(@sI, LEN(@sI) - @x) + '.' + RIGHT(@sI, @x)
SET @sI = dbo.xCutFraction(@sI)
SET @sF = dbo.xFraction(@sI)
IF LEN(@sF) > @n
BEGIN
IF CAST(SUBSTRING(@sF, @n + 1, 1) AS INT) >= 5
BEGIN
IF @n = 0
SET @sI = dbo.AddFraction(LEFT(@sI, LEN(@sF) - @n), '1')
ELSE
SET @sI = dbo.AddFraction(LEFT(@sI, LEN(@sI) - LEN(@sF) + @n), '0.' + REPLICATE('0', @n - 1) + '1')
END
ELSE
SET @sI = LEFT(@sI, LEN(@sI) - LEN(@sF) + @n)
END
SET @sI = dbo.xCutFraction(@sI)
RETURN @sI
END

CREATE FUNCTION MulFraction(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @sI VARCHAR(8000), @xIText VARCHAR(8000), @xFText VARCHAR(8000), @yIText VARCHAR(8000), @yFText VARCHAR(8000), @l INT, @k INT
SELECT @xIText = dbo.xInt(@xText), @yIText = dbo.xInt(@yText), @xFText = dbo.xFraction(@xText), @yFText = dbo.xFraction(@yText)
SELECT @l = LEN(@xFText), @k = LEN(@yFText), @sI = dbo.MulInt(@xIText + @xFText, @yIText + @yFText)
WHILE LEN(@sI) <= @l + @k SET @sI = '0' + @sI
SET @sI = LEFT(@sI, LEN(@sI) - @l - @k) + '.' + RIGHT(@sI, @l + @k)
SET @sI = dbo.xCutFraction(@sI)
RETURN @sI
END

CREATE FUNCTION SubFraction(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @sI VARCHAR(8000), @xIText VARCHAR(8000), @xFText VARCHAR(8000), @yIText VARCHAR(8000), @yFText VARCHAR(8000), @l INT
SELECT @xIText = dbo.xInt(@xText), @yIText = dbo.xInt(@yText), @xFText = dbo.xFraction(@xText), @yFText = dbo.xFraction(@yText)

IF LEN(@xFText) > LEN(@yFText) SET @yFText = @yFText + REPLICATE('0', LEN(@xFText) - LEN(@yFText))
IF LEN(@xFText) < LEN(@yFText) SET @xFText = @xFText + REPLICATE('0', LEN(@yFText) - LEN(@xFText))

SELECT @l = LEN(@xFText), @sI = dbo.SubInt(@xIText + @xFText, @yIText + @yFText)
WHILE LEN(@sI) <= @l SET @sI = '0' + @sI
SET @sI = LEFT(@sI, LEN(@sI) - @l) + '.' + RIGHT(@sI, @l)
SET @sI = dbo.xCutFraction(@sI)
RETURN @sI
END

CREATE FUNCTION AddFraction(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @sI VARCHAR(8000), @xIText VARCHAR(8000), @xFText VARCHAR(8000), @yIText VARCHAR(8000), @yFText VARCHAR(8000), @l INT
SELECT @xIText = dbo.xInt(@xText), @yIText = dbo.xInt(@yText), @xFText = dbo.xFraction(@xText), @yFText = dbo.xFraction(@yText)

IF LEN(@xFText) > LEN(@yFText) SET @yFText = @yFText + REPLICATE('0', LEN(@xFText) - LEN(@yFText))
IF LEN(@xFText) < LEN(@yFText) SET @xFText = @xFText + REPLICATE('0', LEN(@yFText) - LEN(@xFText))

SELECT @l = LEN(@xFText), @sI = dbo.AddInt(@xIText + @xFText, @yIText + @yFText)
WHILE LEN(@sI) <= @l SET @sI = '0' + @sI
SET @sI = LEFT(@sI, LEN(@sI) - @l) + '.' + RIGHT(@sI, @l)
SET @sI = dbo.xCutFraction(@sI)
RETURN @sI
END


CREATE FUNCTION xInt(@xText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @n INT, @strRet VARCHAR(8000)
SET @n = CHARINDEX('.', @xText)
IF @n = 0
SET @strRet = @xText
ELSE
SET @strRet = LEFT(@xText, @n - 1)
RETURN @strRet
END


CREATE FUNCTION xFraction(@xText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @n INT, @strRet VARCHAR(8000)
SET @n = CHARINDEX('.', @xText)
IF @n = 0
SET @strRet = '0'
ELSE
SET @strRet = RIGHT(@xText, LEN(@xText) - @n)
RETURN @strRet
END

CREATE FUNCTION DivInt(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
SELECT @xText = dbo.xCut(@xText), @yText = dbo.xCut(@yText)
DECLARE @i INT,@n INT, @strRet VARCHAR(8000), @strTemp VARCHAR(8000)
SELECT @strRet = '', @strTemp = '', @i = 1
WHILE @i <= LEN(@xText)
BEGIN
SET @strTemp = dbo.xCut(@strTemp + SUBSTRING(@xText, @i, 1))
WHILE dbo.Compare(@strTemp, @yText) = -1 And @i < LEN(@xText)
BEGIN
SELECT @strRet = @strRet + '0', @i = @i + 1, @strTemp = @strTemp + SUBSTRING(@xText, @i, 1)
SET @strTemp = dbo.xCut(@strTemp)
END
IF dbo.Compare(@strTemp, @yText) = -1 AND @i = LEN(@xText)
BEGIN
SELECT @strRet = @strRet + '0', @i = @i + 1
END

IF dbo.Compare(@strTemp, @yText) <> -1
BEGIN
SELECT @n = dbo.xCeiling(@strTemp, @yText)
SELECT @strRet = @strRet + RTRIM(LTRIM(STR(@n))), @strTemp = dbo.SubInt(@strTemp, dbo.MulInt(@yText, RTRIM(LTRIM(STR(@n))))), @i = @i + 1
END
END
SET @strRet = dbo.xCut(@strRet)
RETURN @strRet
END

CREATE FUNCTION xCeiling(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS INT AS
BEGIN
DECLARE @iRet INT
SET @iRet = 0
WHILE dbo.Compare(dbo.MulInt(@yText, RTRIM(LTRIM(STR(@iRet + 1)))), @xText) <> 1
BEGIN
SET @iRet = @iRet + 1
END
RETURN @iRet
END

CREATE FUNCTION MulInt(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @j INT, @l INT, @k INT, @strRet VARCHAR(8000)
SELECT @i = LEN(@xText), @j = LEN(@yText), @strRet = '0'
SELECT @l = LEN(@xText), @k = @j
WHILE @k >= 1
BEGIN
SET @strRet = dbo.AddInt(dbo.MulChar(@xText, SUBSTRING(@yText, @k, 1)) + REPLICATE('0', @j - @k), @strRet)
SET @k = @k - 1
END
RETURN @strRet
END

CREATE FUNCTION MulChar(@xText VARCHAR(8000), @yText VARCHAR(1))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @j INT, @l INT, @m INT, @r INT, @strRet VARCHAR(8000)
SELECT @l = LEN(@xText), @m = 0, @strRet = ''
SET @i = @l
WHILE @i >= 1
BEGIN
SET @r = CAST(SUBSTRING(@xText, @i, 1) AS INT) * CAST(@yText AS INT) + @m
SELECT @m = 0
WHILE @r >= 10
BEGIN
SET @m = @m + 1
SET @r = @r - 10
END
SET @strRet = RTRIM(LTRIM(STR(@r))) + @strRet
SET @i = @i - 1
END
IF @m > 0 SET @strRet = RTRIM(LTRIM(STR(@m))) + @strRet
SET @strRet = dbo.xCut(@strRet)
RETURN @strRet
END

CREATE FUNCTION SubInt(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @l INT, @m INT, @r INT, @strRet VARCHAR(8000)
SET @yText = REPLICATE('0', LEN(@xText) - LEN(@yText)) + @yText
SELECT @l = LEN(@xText), @m = 0, @strRet = ''
SET @i = @l
WHILE @i >= 1
BEGIN
SET @r = 10 + CAST(SUBSTRING(@xText, @i, 1) AS INT) - CAST(SUBSTRING(@yText, @i, 1) AS INT) - @m
SELECT @m = CASE WHEN @r >= 10 THEN 0 ELSE 1 END
SET @strRet = RIGHT(RTRIM(LTRIM(STR(@r))), 1) + @strRet
SET @i = @i - 1
END
SET @strRet = dbo.xCut(@strRet)
RETURN @strRet
END

CREATE FUNCTION AddInt(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @j INT, @l INT, @m INT, @r INT, @strRet VARCHAR(8000)
SELECT @i = LEN(@xText), @j = LEN(@yText)
IF @i < @j SET @xText = REPLICATE('0', @j - @i) + @xText
IF @i > @j SET @yText = REPLICATE('0', @i - @j) + @yText
SELECT @l = LEN(@xText), @m = 0, @strRet = ''
SET @i = @l
WHILE @i >= 1
BEGIN
SET @r = CAST(SUBSTRING(@xText, @i, 1) AS INT) + CAST(SUBSTRING(@yText, @i, 1) AS INT) + @m
SELECT @m = CASE WHEN @r >= 10 THEN 1 ELSE 0 END
SET @strRet = RTRIM(LTRIM(STR(@r - @m*10))) + @strRet
SET @i = @i - 1
END
IF @m > 0 SET @strRet = '1' + @strRet
SET @strRet = dbo.xCut(@strRet)
RETURN @strRet
END

CREATE FUNCTION Compare(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS INT AS
BEGIN
DECLARE @iRet INT, @i INT
IF LEN(@xText) > LEN(@yText) SET @iRet = 1
IF LEN(@xText) < LEN(@yText) SET @iRet = -1
IF LEN(@xText) = LEN(@yText)
BEGIN
SELECT @i = 1, @iRet = 0
WHILE @i <= LEN(@xText)
BEGIN
IF SUBSTRING(@xText, @i, 1) <>SUBSTRING(@yText, @i, 1)
BEGIN
IF SUBSTRING(@xText, @i, 1) > SUBSTRING(@yText, @i, 1)
SET @iRet = 1
ELSE
SET @iRet = -1
BREAK
END
SET @i = @i + 1
END
END
RETURN @iRet
END

CREATE FUNCTION xCut(@s VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
WHILE LEN(@s) > 1 AND LEFT(@s, 1) = '0'
BEGIN
SET @s = RIGHT(@s, LEN(@s) - 1)
END
RETURN @s
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION xCutFraction(@s VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @n INT
SET @n = CHARINDEX('.', @s)
IF @n > 0
BEGIN
SET @i = LEN(@s)
WHILE @i > @n AND SUBSTRING(@s, @i, 1) = '0'
BEGIN
SET @i = @i - 1
END
IF @i = @n SET @i = @i -1
SET @s = LEFT(@s, @i)
END
RETURN @s
END
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site