小写金额转换成大写

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
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 ( 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













CREATE FUNCTION [ dbo ] . [ f_num_chn ] ( @num numeric( 14 , 2 ))
RETURNS varchar ( 100 ) WITH ENCRYPTION
AS
BEGIN
-- 版权所有：pbsql
  DECLARE @n_data VARCHAR ( 20 ), @c_data VARCHAR ( 100 ), @n_str VARCHAR ( 10 ), @i int

  SET @n_data =RIGHT ( SPACE ( 14 ) + 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 ) = ' 00 ' ) or
        (( @n_str = ' 0 ' ) and (( @i = 4 ) or ( @i = 8 ) or ( @i = 12 ) or ( @i = 14 ))))
        SET @c_data = @c_data + SUBSTRING ( ' 零壹贰叁肆伍陆柒捌玖 ' , CAST ( @n_str AS int ) + 1 , 1 )
      IF not (( @n_str = ' 0 ' ) and ( @i <> 4 ) and ( @i <> 8 ) and ( @i <> 12 ))
        SET @c_data = @c_data + SUBSTRING ( ' 仟佰拾亿仟佰拾万仟佰拾圆角分 ' , @i , 1 )
      IF SUBSTRING ( @c_data , LEN ( @c_data ) - 1 , 2 ) = ' 亿万 '
        SET @c_data = SUBSTRING ( @c_data , 1 , LEN ( @c_data ) - 1 )
    END
    SET @i = @i + 1
  END
  IF @num < 0
    SET @c_data = ' （负数） ' + @c_data
  IF @num = 0
    SET @c_data = ' 零圆 '
  IF @n_str = ' 0 '
    SET @c_data = @c_data + ' 整 '
  RETURN ( @c_data )
END
GO