小写转大写金额-数据库专栏,SQL Server

在网上见到一个oracle的版本的小写转大写金额的函数，感觉还不错现在把它转成sql server版本。

/********************************************************
作者：(birdie_7761@cmmail.com)
版本：1.0
创建时间：20020227
修改时间：
功能：小写金额转换成大写
参数：n_lowermoney 小写金额
          v_transtype 种类 — 1: directly translate, 0: read it in words
输出：大写金额
********************************************************/
create function dbo.l2u (@n_lowermoney numeric(15,2),@v_transtype int) 
returns varchar(200) as 
begin
declare @v_lowerstr varchar(200) — 小写金额
declare @v_upperpart varchar(200)
declare @v_upperstr varchar(200) — 大写金额
declare @i_i int

set @v_lowerstr = ltrim(rtrim(round(@n_lowermoney,2))) –四舍五入为指定的精度并删除数据左右空格
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 + 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
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,零拾,零)
set @v_upperstr = replace(@v_upperstr,零佰,零)
set @v_upperstr = replace(@v_upperstr,零仟,零)
set @v_upperstr = replace(@v_upperstr,零零零,零)
set @v_upperstr = replace(@v_upperstr,零零,零)
set @v_upperstr = replace(@v_upperstr,零角零分,整)
set @v_upperstr = replace(@v_upperstr,零分,整)
set @v_upperstr = replace(@v_upperstr,零角,零)
set @v_upperstr = replace(@v_upperstr,零亿零万零元,亿元)
set @v_upperstr = replace(@v_upperstr,亿零万零元,亿元)
set @v_upperstr = replace(@v_upperstr,零亿零万,亿)
set @v_upperstr = replace(@v_upperstr,零万零元,万元)
set @v_upperstr = replace(@v_upperstr,万零元,万元)
set @v_upperstr = replace(@v_upperstr,零亿,亿)
set @v_upperstr = replace(@v_upperstr,零万,万)
set @v_upperstr = replace(@v_upperstr,零元,元)
set @v_upperstr = replace(@v_upperstr,零零,零)
end

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

if ( 零 = substring(@v_upperstr,1,1))
begin
     set @v_upperstr = substring(@v_upperstr,2,(len(@v_upperstr) – 1))
end

if ( 角 = substring(@v_upperstr,1,1))
begin
     set @v_upperstr = substring(@v_upperstr,2,(len(@v_upperstr) – 1))
end

if ( 分 = substring(@v_upperstr,1,1))
begin
     set @v_upperstr = substring(@v_upperstr,2,(len(@v_upperstr) – 1))
end

if (整 = substring(@v_upperstr,1,1))
begin
     set @v_upperstr = 零元整
end
return @v_upperstr
end

例子：

select dbo.l2u(56588441.111,0)

select dbo.l2u(00.00,0)