小写转大写金额_数据库技巧

原作在SQL 2000中有一些问题。修正错误并在SQL 2000中运行通过。

/********************************************************
作者：(wleii165@yahoo.com)
版本：1.0
创建时间：20020227
修改时间：
功能：小写金额转换成大写
参数：n_LowerMoney 小写金额
          v_TransType 种类 — 1: directly translate, 0: read it in words 
输出：大写金额
********************************************************/
CREATE PROCEDURE dbo.L2U 
(
@n_LowerMoney numeric(15,2),
@v_TransType int,
@RET VARCHAR(200) output
)
 AS 
  
Declare @v_LowerStr VARCHAR(200) — 小写金额 
Declare @v_UpperPart VARCHAR(200) 
Declare @v_UpperStr VARCHAR(200) — 大写金额
Declare @i_I int

set nocount on

select @v_LowerStr = LTRIM(RTRIM(STR(@n_LowerMoney,20,2))) –四舍五入为指定的精度并删除数据左右空格

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 + 1,1)
                            WHEN  . THEN  元
                            WHEN  0 THEN  零
                            WHEN  1 THEN  壹
                            WHEN  2 THEN  贰
                            WHEN  3 THEN  叁
                            WHEN  4 THEN  肆
                            WHEN  5 THEN  伍
                            WHEN  6 THEN  陆
                            WHEN  7 THEN  柒
                            WHEN  8 THEN  捌
                            WHEN  9 THEN  玖
                            END
                          + 
                            case @i_I
                            WHEN  1  THEN  分
                            WHEN  2  THEN  角
                            WHEN  3  THEN  
                            WHEN  4  THEN  
                            WHEN  5  THEN  拾
                            WHEN  6  THEN  佰
                            WHEN  7  THEN  仟
                            WHEN  8  THEN  万
                            WHEN  9  THEN  拾
                            WHEN  10  THEN  佰
                            WHEN  11  THEN  仟
                            WHEN  12  THEN  亿
                            WHEN  13  THEN  拾
                            WHEN  14  THEN  佰
                            WHEN  15  THEN  仟
                            WHEN  16  THEN  万
                            ELSE 
                            END
select @v_UpperStr = @v_UpperPart + @v_UpperStr
select @i_I = @i_I + 1
end

——–print  //v_UpperStr =+@v_UpperStr +//

if ( @v_TransType=0 )
begin
select @v_UpperStr = REPLACE(@v_UpperStr,零拾,零) 
select @v_UpperStr = REPLACE(@v_UpperStr,零佰,零) 
select @v_UpperStr = REPLACE(@v_UpperStr,零仟,零) 
select @v_UpperStr = REPLACE(@v_UpperStr,零零零,零)
select @v_UpperStr = REPLACE(@v_UpperStr,零零,零)
select @v_UpperStr = REPLACE(@v_UpperStr,零角零分,整)
select @v_UpperStr = REPLACE(@v_UpperStr,零分,整)
select @v_UpperStr = REPLACE(@v_UpperStr,零角,零)
select @v_UpperStr = REPLACE(@v_UpperStr,零亿零万零元,亿元)
select @v_UpperStr = REPLACE(@v_UpperStr,亿零万零元,亿元)
select @v_UpperStr = REPLACE(@v_UpperStr,零亿零万,亿)
select @v_UpperStr = REPLACE(@v_UpperStr,零万零元,万元)
select @v_UpperStr = REPLACE(@v_UpperStr,万零元,万元)
select @v_UpperStr = REPLACE(@v_UpperStr,零亿,亿)
select @v_UpperStr = REPLACE(@v_UpperStr,零万,万)
select @v_UpperStr = REPLACE(@v_UpperStr,零元,元)
select @v_UpperStr = REPLACE(@v_UpperStr,零零,零)
end

— 对壹元以下的金额的处理 
if ( substring(@v_UpperStr,1,1)=元 )
begin
     select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) – 1))
end

if (substring(@v_UpperStr,1,1)= 零)
begin
     select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) – 1))
end

if (substring(@v_UpperStr,1,1)=角)
begin
     select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) – 1))
end

if ( substring(@v_UpperStr,1,1)=分)
begin
     select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) – 1))
end

if (substring(@v_UpperStr,1,1)=整)
begin
     select @v_UpperStr = 零元整
end

select @ret=@v_UpperStr

GO

调用过程：

declare @ret varchar(200)

exec L2U 567983.897,1,@ret output

select @ret