Prepared by: (www.inbaidu.com)
Creation Time: 20071023
Function: converts the lowercase value to uppercase.
Parameter: @ LowerMoney: up to 38 decimal places can be reserved for the lower-case amount.
Output: capital amount
Introduction: SQL converts the lower-case amount into a large write amount (up to four decimal places can be precise)
Note: The Decimal data type can store up to 38 numbers.
Reprinted: Please keep the above information. Thank you !!!
**************************************** ****************/
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create Function LowerToUpper (@ LowerMoney Decimal (38,4 ))
Returns Varchar (200) -- Returns uppercase characters
As
Begin
Declare @ LowerStr Varchar (50) -- lower case amount
Declare @ UpperStr Varchar (200) -- Capital Amount
Declare @ UpperTmp Varchar (15) -- temporary string of the capital amount
Declare @ I Int -- Increment
Declare @ LowerLen Int -- total length of the lower-case amount
Set @ LowerStr = @ LowerMoney -- assign all values of the Decimal type to the string variable. Note: (if values are assigned in the past, for example, 8 indicates 8.0000 in the string variable because the Decimal point is accurate to four digits, if no, it will automatically add 0)
Set @ LowerStr = Replace (@ LowerStr, '.', '') -- Replace the decimal point with an empty character -- precise to the four decimal places.
Set @ LowerLen = Len (@ LowerStr) -- get the total length of the lower-case amount (including four decimal places)
Select @ I = 1, @ UpperStr = '', @ UpperTmp ='' -- set the default initial value
While @ I <= @ LowerLen
Begin
Set @ UpperTmp = Case
When SubString (@ LowerStr, @ LowerLen-@ I + 1,1) = '0' And @ I = 5 And (Convert (Int, Right (@ LowerStr, 4 )) = 0 Or @ LowerLen> 5) Then 'meta' -- Note: if a single digit is 0, and all four decimal places are 0, Or its length exceeds 5 (that is, it exceeds the dollar ), it is a dollar
-- When SubString (@ LowerStr, @ LowerLen-@ I + 1,1) = '0' then''
Else
+ Case SubString (@ LowerStr, @ LowerLen-@ I +) -- replace the current digit with a traditional Chinese character in upper Case.
When '0' Then '0'
When '1' Then 'yi'
When '2' Then 'er'
When '3' Then 'san'
When '4' then'
When '5' then' Wu'
When '6' Then 'l'
When '7' then' Then'
When '8' then' Then'
When '9' then' Then'
End
+ Case @ I
When 1 Then 'milli'
When 2 Then 'pct'
When 3 Then 'Min'
When 4 Then 'angular'
When 5 Then 'meta'
When 9 Then 'hangzhou'
When 13 Then '100'
When 17 Then 'mega'
When 21 Then 'beijing'
When 25 Then 'hangzhou'
When 29 Then 'hangzhou'
When 33 Then 'hangzhou'
When 37 Then 'trange' -- if the maximum length of the Decimal type is 38, you don't have to consider it.
Else
+ Case @ I % 4
When 2 Then 'start' -- pick up 6 10 14 18 22 26 30 34 38 ............
When 3 Then 'hour' -- Listen 7 11 15 19 23 27 31 35 39 ............
When 0 Then 'two' -- Listen 8 12 16 20 24 28 32 36 40 ............
End
End
End
Set @ UpperStr = Isnull (@ UpperTmp, '') + Isnull (@ UpperStr ,'')
Set @ I = @ I + 1
End
If Convert (Int, Right (@ LowerStr, 4) = 0 Set @ UpperStr = Left (@ UpperStr, Len (@ UpperStr)-8) + 'rounded up '-- determines whether the number of decimal places is 0. If it is 0, it can be rounded up.
While Patindex ('% 0 [] %', @ UpperStr) <> 0 -- converts zero to zero or zero to zero.
Begin
Set @ UpperStr = stuff (@ UpperStr, patindex ('% 0 [] %', @ UpperStr), 2, '0 ')
End
While Patindex ('% [Gou Yu Jing Zhao Yi] 0 [Gou Yu Jing Zhao Yi] %', @ UpperStr) <> 0 -- empty the zero latency or 0 billion
Begin
Select @ UpperStr = Stuff (@ UpperStr, Patindex) + 1, 2 ,'')
End
While Patindex ('% [pick up again] 0 [Break Away When Jing zhaoyi] %', @ UpperStr) <> 0 -- clears the zero in the middle of a collection of zero shards, zero shards, or zero shards.
Begin
Select @ UpperStr = Stuff (@ UpperStr, Patindex ('% [Pick Up]] %', @ UpperStr) + ,'')
End
If Patindex ('% _ 0 [yuan] %', @ UpperStr) <> 0 -- clears zero in the middle of a collection similar to zero or zero.
Begin
Select @ UpperStr = Stuff (@ UpperStr, Patindex ('% _ 0 [RMB] %', @ UpperStr) + ,'')
End
Else If (Patindex ('zero [yuan] % ', @ UpperStr) <> 0) And (Convert (Int, Right (@ LowerStr, 4) <> 0) -- determines whether it is the beginning of zero element, and the last four decimal places are not 0
Begin
Select @ UpperStr = Stuff (@ UpperStr, Patindex ('zero [yuan] % ', @ UpperStr), 2, '') -- clear the zero yuan
End
If Right (@ UpperStr, 1) = '0' Set @ UpperStr = Left (@ UpperStr, Len (@ UpperStr)-1) -- If the last bit is zero, it is cleared.
If @ UpperStr = 'meta-integer 'set @ UpperStr = '0' + @ UpperStr -- If it is only 0, it will show zero yuan.
Return @ UpperStr -- Return the capital amount
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- Call Method
Select dbo. LowerToUpper (120000000) -- one million yuan
Select dbo. LowerToUpper (102000000) -- Yuan
Select dbo. LowerToUpper (100200000) -- one million RMB to collect RMB
Select dbo. LowerToUpper (100020000) -- Yuan
Select dbo. LowerToUpper (100002000) -- one billion yuan