交流三相电流计算公式数字转成大写金额SQL函数--解决思想

2020年11月7日发(作者：谈修)

1.用SQL函数_NumberToChinese换成大写金额，从中把字符替换
2.在天思增加Ado Query加入SQL汇总单据上的金额，再把金额引入SQL函数

_NumberToChinese

3.放出Ado Query 栏位即完成操作。





create FUNCTION [dbo].[fn_NumberToChinese]( @s_money varchar(50)) *数
值类型*
RETURNS VARCHAR(80) AS
BEGIN *FUN*

DECLARE @c_money VARCHAR(12) * s_money *
DECLARE @m_string VARCHAR(22) * 分角元拾佰仟万拾佰仟亿*
DECLARE @n_string VARCHAR(20) * 壹贰叁肆伍陆柒捌玖*
DECLARE @len int * 金额字符串的长度*
DECLARE @is_zero bit * 判断是否为，1－true 0－false *
DECLARE @i int * 循环变*
DECLARE @n char * 金额内各个位的数值*
DECLARE @tmp decimal
DECLARE @b_string VARCHAR(80)
DECLARE @z_count VARCHAR(80)

set @b_string = ''
select @m_string = '分角元拾佰仟万拾佰仟亿拾佰仟万'
select @n_string = '壹贰叁肆伍陆柒捌玖'
select @tmp = ROUND(cast(@s_money as decimal(20,3)),1) * 100 * 去小数
点*
select @c_money = RTRIM(LTRIM(cast(@tmp as varchar(12)))) * 去除首尾空
格*
select @len = len(@c_money) * 获取长度*

IF (@len > 11)
begin
select @b_string = '超出范围'
RETURN @b_string
end

select @is_zero = 1
select @z_count = 0
select @i = 0
WHILE @i < @len
begin * while *
select @i = @i + 1

select @n = substring(@c_money,@i,1) * 逐个去出数字*
IF (@n = '0')
begin
*if no.1*
IF (@len-@i=6 OR @len-@i=2 OR @len=@i)
begin* if no.2 *
IF (@is_zero = 1)
begin* if no.3 *
select @b_string = substring(@b_string,1,len(@b_string)-1)
select @is_zero = 0
end* if no.3 *

IF (@len-@i=6)
begin
select @b_string = @b_string + '万'
end

IF @len-@i=2
begin
select @b_string = @b_string + '元'
end

IF @len = @i
begin
select @b_string = @b_string + '整'
end

select @z_count = 0
end* if no.2 *
ELSE
begin* if no.2 *
IF @z_count = 0
begin
select @b_string = @b_string + '零'
select @is_zero = 1
END
select @z_count = @z_count + 1
end* if no.2 *
end*if no.1*
ELSE
begin*if no.1*
select @b_string = @b_string + substring(@n_string,cast(@n as int),1) +
substring(@m_string,@len-@i+1,1)
select @z_count = 0
select @is_zero = 0
end*if no.1*

END * while *
RETURN @b_string
END *FUN*