http://www.cnblogs.com/Microshaoft/archive/2005/04/02/131008.html
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 --out
declare @L integer
set @L = 4
if @i = @k and @m != 0
begin
set @L = @m
end
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
if @j < @ll - 1
and cast(substring(@ss,(@j+1)+1,1) as integer) > 0
and right(@ss,1) != @num
begin
set @s = @s + @num
end
end
else
begin
set @s = @s + @num + ( 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
if cast(@ss as integer) != 0
begin
set @s = @s + @unit
end
end
else
begin
set @s = @s + @unit
end
set @i = @i - 1 -- out
end --out
return @s
/*
--Test:
select dbo.ConvertNumberToChinese('40000000000')
,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
