SQL function: Lowercase amount converted to uppercase

/********************************************************

Version: 1.0
Creation Time: 20020227
Modification Time:
Function: Lowercase amount converted to uppercase
Parameter: N_lowermoney lowercase amount
V_transtype Type--1:directly translate, 0:read it in words
Output: Uppercase Amount
********************************************************/
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ L2U] ') and xtype in (n ' FN ', n ' IF ', n ' TF '))
Drop function [dbo]. [L2U]
GO

CREATE FUNCTION dbo. L2U (@n_LowerMoney Numeric (15,2), @v_TransType int)
RETURNS VARCHAR (+) as
BEGIN
Declare @v_LowerStr VARCHAR (200)-Lowercase amount
Declare @v_UpperPart VARCHAR (200)
Declare @v_UpperStr VARCHAR (200)--capitalization amount
Declare @i_I int

Set @v_LowerStr = LTRIM (RTRIM (ROUND (@n_LowerMoney, 2))--rounded to the specified precision and deleted data left and right spaces
Set @i_I = 1
Set @v_UpperStr = ' '

while (@i_I <= len (@v_LowerStr))
Begin
Select @v_UpperPart = Case substring (@v_LowerStr, Len (@v_LowerStr)-@i_I + +)
When '. ' Then ' Yuan '
When ' 0 ' then ' 0 '
When ' 1 ' then ' one '
When ' 2 ' Then ' Ii. '
When ' 3 ' then ' three '
When the ' 4 ' then ' the restaurant '
When ' 5 ' Then ' ng '
When ' 6 ' Then ' land '
When ' 7 ' Then ' QI '
When ' 8 ' Then ' BA '
When ' 9 ' Then ' JIU '
END
+
Case @i_I
When 1 Then ' minutes '
When 2 Then ' Corner '
When 3 Then '
When 4 Then '
When 5 Then ' pick '
When 6 then ' Bai '
When 7 Then ' thousand '
When 8 then ' million '
When 9 then ' pick '
When ten then ' Bai '
When one and then ' thousand '
When the then ' billion '
When the "Pick up"
When the then ' Bai '
When the then ' thousand '
When the then ' million '
ELSE '
END
Set @v_UpperStr = @v_UpperPart + @v_UpperStr
Set @i_I = @i_I + 1
End

if (0 = @v_TransType)
Begin
Set @v_UpperStr = REPLACE (@v_UpperStr, ' 0 pickup ', ' 0 ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' 0 bai ', ' 0 ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' 0 thousand ', ' 0 ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' 000 ', ' 0 ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' 00 ', ' 0 ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' Certer ', ' whole ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' 0 points ', ' whole ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' 0 angle ', ' 0 ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' $0 ', ' billion ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' zero million Yuan ', ' billion ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' 0 ', ' billion ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' $0 ', ' million ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' zero Yuan ', ' million ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' 0 ', ' billion ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' 0 ', ' million ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' 0 yuan ', ' meta ')
Set @v_UpperStr = REPLACE (@v_UpperStr, ' 00 ', ' 0 ')
End

--Treatment of the amount below one dollar
if (' meta ' = substring (@v_UpperStr, 1, 1))
Begin
Set @v_UpperStr = substring (@v_UpperStr, 2, (Len (@v_UpperStr)-1))
End

if (' 0 ' = substring (@v_UpperStr, 1, 1))
Begin
Set @v_UpperStr = substring (@v_UpperStr, 2, (Len (@v_UpperStr)-1))
End

if (' angle ' = substring (@v_UpperStr, 1, 1))
Begin
Set @v_UpperStr = substring (@v_UpperStr, 2, (Len (@v_UpperStr)-1))
End

if (' Minute ' = substring (@v_UpperStr, 1, 1))
Begin
Set @v_UpperStr = substring (@v_UpperStr, 2, (Len (@v_UpperStr)-1))
End

if (' whole ' = substring (@v_UpperStr, 1, 1))
Begin
Set @v_UpperStr = ' 0 yuan whole '
End
Return @v_UpperStr
END
GO

SELECT dbo. L2U (12.93,1), dbo. L2U (12.93,0)
?



CREATE PROCEDURE dbo. L2u
(
@n_LowerMoney Numeric (15,2),
@v_TransType int,
@RET VARCHAR ($) output
)
As

Declare @v_LowerStr VARCHAR (200)-Lowercase amount
Declare @v_UpperPart VARCHAR (200)
Declare @v_UpperStr VARCHAR (200)--capitalization amount
Declare @i_I int

SET NOCOUNT ON

Select @v_LowerStr = LTRIM (RTRIM (STR (@n_LowerMoney, 20,2))--rounded to the specified precision and deleted data left and right spaces

Select @i_I = 1
Select @v_UpperStr = ""

while (@i_I <= len (@v_LowerStr))
Begin
Select @v_UpperPart = Case substring (@v_LowerStr, Len (@v_LowerStr)-@i_I + +)
When '. ' Then ' Yuan '
When ' 0 ' then ' 0 '
When ' 1 ' then ' one '
When ' 2 ' Then ' Ii. '
When ' 3 ' then ' three '
When the ' 4 ' then ' the restaurant '
When ' 5 ' Then ' ng '
When ' 6 ' Then ' land '
When ' 7 ' Then ' QI '
When ' 8 ' Then ' BA '
When ' 9 ' Then ' JIU '
END
+
Case @i_I
When 1 Then ' minutes '
When 2 Then ' Corner '
When 3 Then '
When 4 Then '
When 5 Then ' pick '
When 6 then ' Bai '
When 7 Then ' thousand '
When 8 then ' million '
When 9 then ' pick '
When ten then ' Bai '
When one and then ' thousand '
When the then ' billion '
When the "Pick up"
When the then ' Bai '
When the then ' thousand '
When the then ' million '
ELSE ' '
END
Select @v_UpperStr = @v_UpperPart + @v_UpperStr
Select @i_I = @i_I + 1
End

--------print '//v_upperstr = ' [email protected]_upperstr + '//'

if (@v_TransType = 0)
Begin
Select @v_UpperStr = REPLACE (@v_UpperStr, ' 0 pickup ', ' 0 ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' 0 bai ', ' 0 ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' 0 thousand ', ' 0 ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' 000 ', ' 0 ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' 00 ', ' 0 ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' Certer ', ' whole ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' 0 points ', ' whole ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' 0 angle ', ' 0 ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' $0 ', ' billion ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' zero million Yuan ', ' billion ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' 0 ', ' billion ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' $0 ', ' million ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' zero Yuan ', ' million ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' 0 ', ' billion ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' 0 ', ' million ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' 0 yuan ', ' yuan ')
Select @v_UpperStr = REPLACE (@v_UpperStr, ' 00 ', ' 0 ')
End

--Treatment of the amount below one dollar
if (substring (@v_UpperStr, 1, 1) = ' Yuan ')
Begin
Select @v_UpperStr = substring (@v_UpperStr, 2, (Len (@v_UpperStr)-1))
End

if (substring (@v_UpperStr, 1, 1) = ' 0 ')
Begin
Select @v_UpperStr = substring (@v_UpperStr, 2, (Len (@v_UpperStr)-1))
End

if (substring (@v_UpperStr, 1, 1) = ' angle ')
Begin
Select @v_UpperStr = substring (@v_UpperStr, 2, (Len (@v_UpperStr)-1))
End

if (substring (@v_UpperStr, 1, 1) = ' min ')
Begin
Select @v_UpperStr = substring (@v_UpperStr, 2, (Len (@v_UpperStr)-1))
End

if (substring (@v_UpperStr, 1, 1) = ' whole ')
Begin
Select @v_UpperStr = ' 0 yuan whole '
End

Select @[email protected]_upperstr

GO

--Call procedure:

DECLARE @ret varchar (200)

exec l2u 567983.897,1, @ret output

Select @ret


CREATE FUNCTION [dbo]. [F_num_chn] (@num numeric (14,2))
RETURNS varchar (+) with encryption
As
BEGIN
--All rights reserved: Pbsql
DECLARE @n_data varchar, @c_data varchar (+), @n_str varchar (ten), @i int

SET @n_data =right (SPACE) +cast (CAST (ABS (@num *100) as bigint) as varchar (20)), 14)
SET @c_data = ' '
SET @i=1
While @i<=14
BEGIN
SET @n_str =substring (@n_data, @i,1)
IF @n_str <> '
BEGIN
IF Not ((SUBSTRING (@n_data, @i,2) = ' xx ') or
((@n_str = ' 0 ') and ((@i=4) or (@i=8) or (@i=12) or (@i=14)))
SET @[email protected]_data+substring (' 0 a three-Woolu qi Ba Jiu ', CAST (@n_str as int) +1,1)
IF not ((@n_str = ' 0 ') and (@i<>4) and (@i<>8) and (@i<>12))
SET @[email protected]_data+substring (' Qian Bai hundred million thousand hundred thousand gather round corner cent ', @i,1)
IF SUBSTRING (@c_data, LEN (@c_data) -1,2) = ' billions '
SET @c_data =substring (@c_data, 1,len (@c_data)-1)
END
SET @[email protected]+1
END
IF @num <0
SET @c_data = ' (negative number) ' [Email protected]_data
IF @num =0
SET @c_data = ' 0 Circle '
IF @n_str = ' 0 '
SET @[email protected]_data+ ' whole '
RETURN (@c_data)
END
GO

SQL function: Lowercase amount converted to uppercase