Convert lowercase to uppercase-Stored Procedure

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.