--增加处理 1013 一千零"十三", 1113 一千一百"一十三"
create function ConvertNumberToChinese(@ varchar(100))
returns varchar(100)
as
begin
--declare @ varchar(100)
--set @ = '101140101031013'
declare @s varchar(100)
set @s = ''
declare @p integer
set @p = 0
declare @m integer
set @m = len(@) % 4
declare @k integer
if @m > 0
begin
set @k = len(@)/4 + 1
end
else
begin
set @k = len(@)/4
end
declare @i integer
set @i = @k
while (@i > 0)
begin --outer
declare @L integer
set @L = 4 select @L = @m
where @i = @k and @m != 0 declare @ss varchar(4)
set @ss = substring(@,@p+1,@L)
declare @ll integer
set @ll = len(@ss) --inner
declare @j integer
set @j = 0
while (@j < @ll) --inner
begin --inner
declare @n integer
set @n = cast(substring(@ss,@j+1,1) as integer)
declare @num varchar(2)
set @num =( select Num
from
(
select 0 as id,'零' as Num
union all select 1,'壹'
union all select 2,'贰'
union all select 3,'叁'
union all select 4,'肆'
union all select 5,'伍'
union all select 6,'陆'
union all select 7,'柒'
union all select 8,'捌'
union all select 9,'玖'
) Nums
where id = @n
)
if @n = 0
begin
select @s = @s + @num
where @j < @ll - 1
and cast(substring(@ss,(@j+1)+1,1) as integer) > 0
and right(@ss,1) != @num
end
else
begin
declare @jj integer
set @jj = 1
set @jj = (select @j - 1 where @j > 1)
select @s = @s + @num
where not (@n = 1
and @j = @ll - 2
and (len(@s) = 0
or right(@s,1) = '零'
/*
( select Num
from
(
select 0 as id,'零' as Num
union all select 1,'壹'
union all select 2,'贰'
union all select 3,'叁'
union all select 4,'肆'
union all select 5,'伍'
union all select 6,'陆'
union all select 7,'柒'
union all select 8,'捌'
union all select 9,'玖'
) Nums
where id = 0
)
*/
) ) set @s = @s + ( select digit
from
(
select 0 as id,'' as digit
union all select 1,'拾'
union all select 2,'佰'
union all select 3,'仟'
) digits
where id = @ll - @j - 1
)
end
set @j = @j + 1 --inner
end --inner
set @p = @p + @L declare @unit varchar(10)
set @unit = ( select Unit
from
(
select 0 as id,'' as Unit
union all select 1,'[万]'
union all select 2,'[亿]'
union all select 3,'[万亿]'
) Units
where id = @i - 1
)
if @i < @k
begin
select @s = @s + @unit
where cast(@ss as integer) != 0
end
else
begin
set @s = @s + @unit
end
set @i = @i - 1 -- outer
end --out
return @s
/*
--Test:
select dbo.ConvertNumberToChinese('1011111112100113')
,dbo.ConvertNumberToChinese('40000000001')
,dbo.ConvertNumberToChinese('400000010000')
,dbo.ConvertNumberToChinese('40101031013')
,dbo.ConvertNumberToChinese('101140101031013')
,dbo.ConvertNumberToChinese('100000001000003')
,dbo.ConvertNumberToChinese('10011003')
,dbo.ConvertNumberToChinese('10010103')
,dbo.ConvertNumberToChinese('10010013')
*/
end
create function ConvertNumberToChinese(@ varchar(100))
returns varchar(100)
as
begin
--declare @ varchar(100)
--set @ = '101140101031013'
declare @s varchar(100)
set @s = ''
declare @p integer
set @p = 0
declare @m integer
set @m = len(@) % 4
declare @k integer
if @m > 0
begin
set @k = len(@)/4 + 1
end
else
begin
set @k = len(@)/4
end
declare @i integer
set @i = @k
while (@i > 0)
begin --outer
declare @L integer
set @L = 4 select @L = @m
where @i = @k and @m != 0 declare @ss varchar(4)
set @ss = substring(@,@p+1,@L)
declare @ll integer
set @ll = len(@ss) --inner
declare @j integer
set @j = 0
while (@j < @ll) --inner
begin --inner
declare @n integer
set @n = cast(substring(@ss,@j+1,1) as integer)
declare @num varchar(2)
set @num =( select Num
from
(
select 0 as id,'零' as Num
union all select 1,'壹'
union all select 2,'贰'
union all select 3,'叁'
union all select 4,'肆'
union all select 5,'伍'
union all select 6,'陆'
union all select 7,'柒'
union all select 8,'捌'
union all select 9,'玖'
) Nums
where id = @n
)
if @n = 0
begin
select @s = @s + @num
where @j < @ll - 1
and cast(substring(@ss,(@j+1)+1,1) as integer) > 0
and right(@ss,1) != @num
end
else
begin
declare @jj integer
set @jj = 1
set @jj = (select @j - 1 where @j > 1)
select @s = @s + @num
where not (@n = 1
and @j = @ll - 2
and (len(@s) = 0
or right(@s,1) = '零'
/*
( select Num
from
(
select 0 as id,'零' as Num
union all select 1,'壹'
union all select 2,'贰'
union all select 3,'叁'
union all select 4,'肆'
union all select 5,'伍'
union all select 6,'陆'
union all select 7,'柒'
union all select 8,'捌'
union all select 9,'玖'
) Nums
where id = 0
)
*/
) ) set @s = @s + ( select digit
from
(
select 0 as id,'' as digit
union all select 1,'拾'
union all select 2,'佰'
union all select 3,'仟'
) digits
where id = @ll - @j - 1
)
end
set @j = @j + 1 --inner
end --inner
set @p = @p + @L declare @unit varchar(10)
set @unit = ( select Unit
from
(
select 0 as id,'' as Unit
union all select 1,'[万]'
union all select 2,'[亿]'
union all select 3,'[万亿]'
) Units
where id = @i - 1
)
if @i < @k
begin
select @s = @s + @unit
where cast(@ss as integer) != 0
end
else
begin
set @s = @s + @unit
end
set @i = @i - 1 -- outer
end --out
return @s
/*
--Test:
select dbo.ConvertNumberToChinese('1011111112100113')
,dbo.ConvertNumberToChinese('40000000001')
,dbo.ConvertNumberToChinese('400000010000')
,dbo.ConvertNumberToChinese('40101031013')
,dbo.ConvertNumberToChinese('101140101031013')
,dbo.ConvertNumberToChinese('100000001000003')
,dbo.ConvertNumberToChinese('10011003')
,dbo.ConvertNumberToChinese('10010103')
,dbo.ConvertNumberToChinese('10010013')
*/
end
returns varchar(100)
as
begin
--declare @ varchar(100)
--set @ = '101140101031013'
declare @s varchar(100)
set @s = ''declare @p integer
set @p = 0declare @m integer
set @m = len(@) % 4declare @k integer
set @k = len(@)/4select @k = @k + 1
where @m > 0declare @i integer
set @i = @kwhile (@i > 0)
begin --outer
declare @L integer
set @L = 4 select @L = @m
where @i = @k and @m != 0 declare @ss varchar(4)
set @ss = substring(@,@p+1,@L) declare @ll integer
set @ll = len(@ss) --inner
declare @j integer
set @j = 0 while (@j < @ll) --inner
begin --inner
declare @n integer
set @n = cast(substring(@ss,@j+1,1) as integer) declare @num varchar(2)
select @num = Num
from (
select 0 as id,'零' as Num
union all select 1,'壹'
union all select 2,'贰'
union all select 3,'叁'
union all select 4,'肆'
union all select 5,'伍'
union all select 6,'陆'
union all select 7,'柒'
union all select 8,'捌'
union all select 9,'玖'
) Nums
where id = @n if @n = 0
begin
select @s = @s + @num
where @j < @ll - 1
and cast(substring(@ss,(@j+1)+1,1) as integer) > 0
and right(@ss,1) != @num
end
else
begin
declare @jj integer
set @jj = 1 select @jj = @j - 1
where @j > 1 select @s = @s + @num
where not (@n = 1
and @j = @ll - 2
and (len(@s) = 0
or right(@s,1) = '零'
/*
( select Num
from
(
select 0 as id,'零' as Num
union all select 1,'壹'
union all select 2,'贰'
union all select 3,'叁'
union all select 4,'肆'
union all select 5,'伍'
union all select 6,'陆'
union all select 7,'柒'
union all select 8,'捌'
union all select 9,'玖'
) Nums
where id = 0
)
*/
)
) select @s = @s + digit
from (
select 0 as id,'' as digit
union all select 1,'拾'
union all select 2,'佰'
union all select 3,'仟'
) digits
where id = @ll - @j - 1 end
set @j = @j + 1 --inner
end --inner
set @p = @p + @L declare @unit varchar(10)
select @unit = Unit
from (
select 0 as id,'' as Unit
union all select 1,'[万]'
union all select 2,'[亿]'
union all select 3,'[万亿]'
) Units
where id = @i - 1 if @i < @k
begin
select @s = @s + @unit
where cast(@ss as integer) != 0
end
else
begin
set @s = @s + @unit
end
set @i = @i - 1 -- outer
end --out
return @s
/*
--Test:
select dbo.ConvertNumberToChinese('1011111112100113')
,dbo.ConvertNumberToChinese('40000000001')
,dbo.ConvertNumberToChinese('400000010000')
,dbo.ConvertNumberToChinese('40101031013')
,dbo.ConvertNumberToChinese('101140101031013')
,dbo.ConvertNumberToChinese('100000001000003')
,dbo.ConvertNumberToChinese('10011003')
,dbo.ConvertNumberToChinese('10010103')
,dbo.ConvertNumberToChinese('10010013')
*/
end
PARAMETERS lnMoney
lnNumber = lnMoney
ChinaMoney = ""
DIME ChinaNo(10)
ChinaNo(1) = "錨"
ChinaNo(2) = "瓞"
ChinaNo(3) = "楚"
ChinaNo(4) = ""
ChinaNo(5) = "佹"
ChinaNo(6) = "斪"
ChinaNo(7) = "翻"
ChinaNo(8) = ""
ChinaNo(9) = "副"
ChinaNo(10) = "墾"
DIME Cdw(2)
Cdw(1)="褒"
Cdw(2)="煦"
DIME ChinaNum(10)
ChinaNum(1) = "啋"
ChinaNum(2) = "夆"
ChinaNum(3) = "唱"
ChinaNum(4) = ""
ChinaNum(5) = "勀"
ChinaNum(6) = "夆"
ChinaNum(7) = "唱"
ChinaNum(8) = ""
ChinaNum(9) = "砬"
ChinaNum(10) = "夆"
lnIntNumber = INT(lnNumber)
lcIntNumber = ALLTRIM(STR(lnIntNumber,10,0))
lnDicNumber = Int((lnNumber-lnIntNumber)*100)
lcDicNumber = IIF(!EMPTY(lnDicNumber),STR(lnDicNumber,2,0),"")N = 1
FOR I = LEN(lcIntNumber) TO 1 STEP -1
FOR J = 1 TO 10
IF VAL(SUBST(lcIntNumber,I,1)) = J-1
ChinaMoney = ChinaNo(J)+ChinaNum(N)+ChinaMoney
N = N+1
EXIT
ENDIF
ENDFOR
ENDFOR
IF !EMPTY(lcDicNumber)
FOR Z = 1 TO 2
FOR P = 1 TO 10
IF VAL(SUBST(lcDicNumber,Z,1)) = P-1
ChinaMoney = ChinaMoney+ChinaNo(P)+Cdw(Z)
EXIT
ENDIF
ENDFOR
ENDFOR
ELSE
ChinaMoney=ChinaMoney+"淕"
ENDIF
RETURN ChinaMoney