MSSQL digital Money converted to uppercase

--Description:--1. This function ranges from Milli to trillion--2. There are four kinds of precision (yuan, angle, minute, cl, mm)--3. There are three kinds of rounding rules (rounding, picking, not 0)--parameter description: dbo. Moneytocapital (numeric, carry, precision)--carry (0 rounding, 1 direct, 2 non 0 in)--accuracy (0 yuan, 1 cents, 2 points, 3 cl, 4 mm)------------------------------------ ----------------------------------------------------------------------test data: DECLARE @intNum decimal (38,4) SET @ Intnum = 1123456780.2154--set @intNum = 1001--set @intNum = 100100--set @intNum = 1005001--set @intNum = 100.11--set @intN Um = 100.00--set @intNum = 100.01SET @intNum = 99999999999999.9999--Maximum <1 (accurate to milli)--set @intNum = 10025.1234--set @int Num = 12345.6789SELECT dbo. Moneytocapital (@intNum, 0,4)----------------------------------------------------------------------------------- ---------------------gocreate FUNCTION moneytocapital (@mnyNumber decimal (38,4), @intIsRound int = 0,--carry (0 rounding, 1 direct,  2 non-0 in) @intPrecision int = 2--accuracy: 0 yuan, 1 pt, 2 min, 3 cl, 4 mm) RETURNS nvarchar () begindeclare @strReturn nvarchar (DECLARE) @strMoney varchar (DECLARE) @intLen intdeclare @strC1 char (1) DEclare @strC2 char (1) DECLARE @strC3 char (1) DECLARE @intJ intdeclare @necMoney Decimal (38,4) DECLARE @strMoneyUnit nvarchar () DECLARE @strNumberCapital nvarchar () Set @strMoneyUnit = ' least bit split-angle element pick-up bai thousand thousand billion hundred thousand trillion to pick Bai Qian ' SET @ Strnumbercapital = ' 0 One and three Woolu qi Ba Jiu '--0 if @mnyNumber = 0 BEGIN SET @strReturn = ' 0 ' RETURN @strReturnEND--out of range if @mnyNumber < 0 OR @mnyNumber > 99999999999999.9999 BEGIN RETURN CAST (@mnyNumber as varchar ()) END------carry out of range I F @intIsRound <0 or @intIsRound >2 BEGIN SET @intIsRound = 0END------accuracy out of range if @intPrecision <0 or @intPrecision & Gt;4 BEGIN Set @intPrecision = 2ENDIF @intIsRound =1 begin--directly out of Set @mnyNumber = ROUND (@mnyNumber, 2,1) endelse IF @int Isround = 2 begin--not 0 just into set @mnyNumber = ROUND (@mnyNumber, 2) endelse begin--rounding Set @mnyNumber = ROUND (@mnyNumber, @int  Precision) Endset @necMoney = @mnyNumber * POWER (@intPrecision)--accuracy @intPrecisionSET @strMoney = cast (CAST (@necMoney as bigint) as varchar (()) SET @intLen = LEN (@strMoney)--Length SET @strMoney = REVERSE (@strMoney)--Reverse Set @strReturn = ' Set @intJ = 1--@intPrecision accuracy: 0 yuan, 1 angle, 2 points, 3 cl, 4 MM (1) while @intJ <= @intLen begin set @strC1 = SUBSTRING (@strMoney, @intJ -1,1) Set @strC2 = SUBSTRING (@strMon EY, @intJ, 1) Set @strC3 = SUBSTRING (@strMoney, @intJ +1,1) Set @strReturn = SUBSTRING (@strNumberCapital, CAST (@strC2 as int) +1, 1) +substring (@strMoneyUnit, @[email protected], 1) + @strReturn Set @intJ = @intJ + 1END--replace the extra part set @ RETURNSTR = replace (@returnStr, ' 0 ', ' 0 ') Set @returnStr = replace (@returnStr, ' 0 ', ' 0 ') Set @returnStr = replace (@ Returnstr, ' 0 ', ' 0 ') Set @returnStr = replace (@returnStr, ' 0 pickup ', ' 0 ') Set @returnStr = replace (@returnStr, ' 000 ', ' 0 ') set @ RETURNSTR = replace (@returnStr, ' 00 ', ' 0 ') Set @returnStr = replace (@returnStr, ' 0 ', ' million ') set @returnStr = replace (@ Returnstr, ' 0 yuan ', ' yuan ') set @returnStr = replace (@returnStr, ' 0 corner ', ' 0 ') Set @returnStr = replace (@returnStr, ' 0 ', ') while Left (@returnStr, 1) = ' 0 ' Begin set @returnStr = Right (@returnStr,len (@returnStr)-1) endif ((Left (@returnStr, 1) = ' million ')) begin set @returnStr = Right (@returnStr, Len (@returnStr)-1) EndIf ((Left (@returnStr, 1) = ' yuan ')) begin set @returnStr = Right (@returnStr, Len (@returnStr)-1) Endwhile left (@returnStr, 1 ) = ' 0 ' begin set @returnStr = Right (@returnStr, Len (@returnStr)-1) Endwhile Right (@returnStr, 1) = ' 0 ' begin set @returnStr = Left (@returnStr, Len (@returnStr)-1) Endset @returnStr = replace (@returnStr, ' billions ', ' billion Zero ') set @returnStr = replace (@ Returnstr, ' 0 yuan ', ' yuan ') set @returnStr = replace (@returnStr, ' 000 ', ' 0 ') Set @returnStr = replace (@returnStr, ' 00 ', ' 0 ') if (@ Returnstr= ") begin set @returnStr = ' $0 ' endif ((Right (@returnStr, 1) = ' yuan ')) begin set @returnStr = @returnStr + ' whole ' end　　 RETURN @strReturnEND--string1 = "0 One and three Woolu qi ba jiu"--STRING2 = "thousands of thousand hundred million thousand to pick up the yuan angle of the hundred cents"

　　

MSSQL digital Money converted to uppercase