SQL conversion in uppercase

Find the corresponding uppercase information in the specified character

CREATE function Get_StrArrayStrOfIndex
(
@ Str varchar (1024), -- string to be split
@ Split varchar (10), -- Separator
@ Index int -- obtains the nth element.
)
Returns varchar (1024)
As
Begin
Declare @ location int
Declare @ start int
Declare @ next int
Declare @ seed int
Set @ str = ltrim (rtrim (@ str ))
Set @ start = 1
Set @ next = 1
Set @ seed = len (@ split)
Set @ location = charindex (@ split, @ str)
While @ location <> 0 and @ index> @ next
Begin
Set @ start = @ location + @ seed
Set @ location = charindex (@ split, @ str, @ start)
Set @ next = @ next + 1
End
If @ location = 0 select @ location = len (@ str) + 1

-- There are two cases: 1. There is no Separator in the string. 2. There is a separator in the string. After jumping out of the while LOOP, @ location is 0, by default, there is a separator behind the string.
Return substring (@ str, @ start, @ location-@ start)
End

 

In the SQL process, the preceding process is called.

If (Exists (select * from SysObjects where [name] = 'fn _ GetEnglishMoney '))
Drop function fn_GetEnglishMoney
Go

Create Function fn_GetEnglishMoney (@ num Numeric (18, 2 ))
Returns nvarchar (500)
As begin
Declare @ arr1 nvarchar (1000), @ arr2 nvarchar (1000 ),
@ Arr3 nvarchar (1000), @ arr4 nvarchar (1000)

Select @ arr1 = ', THOUSAND, MILLION, BILLION ',
@ Arr2 = 'Zero, TEN, TWENTY, THIRTY, FORTY, Ty, SIXTY, SEVENTY, EIGHTY, ninety ',
@ Arr3 = 'Zero, ONE, TWO, THREE, FOUR, FIVE, SIX, SEVEN, EIGHT, nine ',
@ Arr4 = 'Ten, ELEVEN, TWELVE, THIRTEEN, FOURTEEN, FIFTEEN, SIXTEEN, SEVENTEEN, EIGHTEEN, NINETEEN'

Declare @ Inum nvarchar (100), @ DNum nvarchar (10)
-- Decomposition value
Set @ Inum = Convert (nvarchar (100), floor (@ Num ))
Set @ DNum = Right (Convert (nvarchar (100), floor (@ Num * 100), 2)

-- Return @ INum + ''+ @ DNum
-- Integer Conversion
Declare @ Len int -- integer Length
Declare @ I int, @ j int -- number of loops
Declare @ Cols int -- number of cycles
Declare @ Start int -- Start value
Declare @ strR nvarchar (500) -- intermediate Return Value
Declare @ strReturn nvarchar (500) -- Return Value
Declare @ Num3 nvarchar (500) -- transitional operation value
Declare @ Col int

Set @ Len = len (@ Inum)
Set @ Cols = ceiling (cast (@ len as float)/3)
Set @ Start = @ Len-@ Cols * 3
Set @ I = @ start
Set @ strReturn =''
Set @ j = 0

While (@ I <@ len)
Begin
Set @ strR =''
-- Record the transitional operation value
If (@ I> = 0) begin
If (@ I + 3 <@ Len)
Set @ Num3 = substring (@ INum, @ I + 1, 3)
Else
Set @ Num3 = substring (@ INum, @ I + 1, @ Len-@ I)
End
Else
Set @ Num3 = substring (@ INum, 1, @ I + 3)
-- The value is complete.
-- Whether there are hundreds of digits. If so, write down
If (Len (@ Num3) = 3 and @ Num3 <> '000') begin
If (substring (@ Num3, 1, 1) <> '0 ')
Set @ StrR = @ StrR + dbo. get_StrArrayStrOfIndex (@ arr3, ',', Convert (int, substring (@ Num3,) + 1) + 'hundred' -- get number in arr3
If (substring (@ Num3, 2, 2) <> '00 ')
Set @ StrR = @ StrR + 'and'
Set @ Num3 = substring (@ num3, 2, Len (@ Num3)-1)
End
-- Confirm 10 digits
If (len (@ num3) = 2) begin
If (Substring (@ Num3, 1, 1) = '0 ')
Set @ Num3 = substring (@ num3, 2, Len (@ Num3)-1)
Else if (substring (@ Num3, 1, 1) = '1 ')
Set @ strR = @ strR + ''+ dbo. get_StrArrayStrOfIndex (@ arr4, ',', Convert (int, substring (@ Num3, 2, 1) + 1) -- get the number in arr4
Else begin
Set @ strR = @ strR + ''+ dbo. get_StrArrayStrOfIndex (@ arr2, ',', Convert (int, substring (@ Num3, 1, 1) + 1) -- get the number in arr2
If (substring (@ Num3, 2, 1) <> '0 ')
Set @ StrR = @ StrR + '-'
Set @ Num3 = substring (@ num3, 2, Len (@ Num3)-1)
End
End
-- Confirm the single digit
If (Len (@ Num3) = 1 and substring (@ Num3, 1,1) <> '0') begin
Set @ StrR = @ StrR + ''+ dbo. get_StrArrayStrOfIndex (@ arr3, ',', Convert (int, substring (@ Num3, 1, 1) + 1) -- get the number in arr3
End

Set @ StrR = @ StrR + ''+ dbo. Get_StrArrayStrOfIndex (@ arr1, ',', @ Cols-@ j) + ','
Set @ j = @ j + 1

Set @ strReturn = @ strReturn + isnull (@ StrR ,'')
 
Set @ I = @ I + 3
End

If (@ Dnum <> '00') begin
Set @ StrReturn = @ StrReturn + 'point' +
Dbo. Get_StrArrayStrOfIndex (@ arr2, ',', Convert (int, substring (@ Dnum, 1, 1) + 1)
If (Substring (@ dnum, 2, 1) <> '0 ')
Set @ StrReturn = @ StrReturn + '-'
Set @ StrReturn = @ StrReturn +
Dbo. Get_StrArrayStrOfIndex (@ arr3, ',', Convert (int, substring (@ Dnum, 1, 1) + 1)
End
 
Return @ strReturn + 'only'

End

Go

Select dbo. fn_GetEnglishMoney (1812525124.36)