关于数字金额转换大写金额，sql语句

ALTER FUNCTION [dbo].[NumToChn] (@num numeric(19,6))

RETURNS nvarchar(100)

AS

BEGIN

DECLARE @n_data nVARCHAR(20),@c_data nVARCHAR(100),@n_str nVARCHAR(10),@i int

SET @n_data=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100) AS bigint) AS nvarchar(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( N'零壹贰叁肆伍陆柒捌玖',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( N'仟佰拾亿仟佰拾万仟佰拾圆角分',@i,1)

IF SUBSTRING(@c_data,LEN(@c_data)-1,2)= N'亿万'

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

建立一个函数，函数如下

CREATE FUNCTION [dbo].[f_num_chn] (@num numeric(14,5))

RETURNS varchar(100) WITH ENCRYPTION

AS

BEGIN

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

调用

select [dbo].[f_num_chn](120)

创建一个存储过程就行了

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

---