Codecreate function decodehundred(@hundredstring varchar(300) )
returns varchar(1000)
as
begin
declare @tmp varchar(1000),@decodehundred varchar(1000)
set @decodehundred = ''
declare @strno1 varchar(1000)
select @strno1 = 'One Two Three Four Five ' +
'Six Seven Eight Nine Ten ' +
'Eleven Twelve Thirteen Fourteen Fifteen '+
'Sixteen Seventeen Eighteen Nineteen '
declare @strtens1 varchar(1000)
set @strtens1 = 'Ten Twenty Thirty Forty Fifty ' +
'Sixty Seventy Eighty Ninety '
declare @unit4 varchar(10)
set @unit4 = 'Hundred'
if len(@hundredstring) > 0 and len(@hundredstring) <= 3
begin
if len(@hundredstring) = 1
begin
set @tmp = cast(@hundredstring as int)
if @tmp <> 0
set @decodehundred =
rtrim(substring(@strno1 ,(@tmp - 1) * 10 + 1,10))
end
if len(@hundredstring) = 2
begin
set @tmp = cast(@hundredstring as int)
if @tmp <> 0
if @tmp < 20
if @tmp < 10
set @decodehundred = 'Zero '+rtrim(substring(@strno1 ,(@tmp - 1) * 10 + 1,10))
else
set @decodehundred =
rtrim(substring(@strno1 ,(@tmp - 1) * 10 + 1,10))
else
if cast(right(@hundredstring, 1) as int ) = 0
set @decodehundred = rtrim(substring(@strtens1 ,(@tmp/10 - 1) * 10 + 1,10))
else
set @decodehundred = rtrim(substring(@strtens1 ,(@tmp/10 - 1) * 10 + 1,10))
+ '-' + rtrim(substring(@strno1 ,(cast(right(@hundredstring, 1) as int) -1) * 10 + 1,10))
end
if len(@hundredstring) = 3
begin
if cast(left(@hundredstring, 1) as int) <> 0
set @decodehundred = rtrim(substring(@strno1 ,(cast(left(@hundredstring, 1) as int)- 1) * 10 + 1,10))
+ ' ' + @unit4 + ' ' + dbo.decodehundred(right(@hundredstring, 2))
else
set @decodehundred = dbo.decodehundred(right(@hundredstring, 2))
end
end
return @decodehundred
end
go
create function f_numbertostring(@number as numeric(12,2))
returns varchar(1000)
as
begin
declare @str varchar(100), @beforepoint varchar(100), @afterpoint varchar(100), @tmpstr varchar(100),@numbertostring varchar(200)
declare @point int,@nnumlen int
declare @nbit integer
declare @curstring varchar(100)
set @numbertostring = ''
declare @unit1 varchar(1000)
set @unit1 = 'Thousand Million Billion Hundred Only Point * And '
set @str = cast(round(@number, 2) as varchar)
if charindex('.', @str) = 0
begin
set @beforepoint = @str
set @afterpoint = ''
end
else
begin
begin
set @beforepoint = left(@str, charindex('.', @str) - 1)
set @afterpoint = right(@str, len(@str) - charindex('.', @str))
end
if len(@beforepoint) > 12
begin
set @numbertostring = 'Too big.'
return @numbertostring
end
set @str = ''
while len(@beforepoint) > 0
begin
set @nnumlen = len(@beforepoint)
if @nnumlen % 3 = 0
begin
set @curstring = left(@beforepoint, 3)
set @beforepoint = right(@beforepoint, @nnumlen - 3)
end
else
begin
set @curstring = left(@beforepoint, (@nnumlen % 3))
set @beforepoint = right(@beforepoint, @nnumlen - (@nnumlen % 3))
end
set @nbit = len(@beforepoint) / 3
set @tmpstr = dbo.decodehundred(@curstring)
if (@beforepoint = REPLICATE ('0',len(@beforepoint)) or @nbit = 0) and len(@curstring) = 3
if cast(left(@curstring, 1) as int) <> 0 and cast(right(@curstring, 2) as int) <> 0
set @tmpstr = left(@tmpstr,charindex('Hundred', @tmpstr) + len('Hundred')) + 'And ' +
right(@tmpstr, len(@tmpstr) - (charindex('Hundred',@tmpstr ) + 7))
else if cast(left(@curstring, 1) as int) <> 0 and cast(right(@curstring, 2) as int) = 0
set @tmpstr = 'And ' + @tmpstr
if @nbit = 0
set @str = ltrim(@str + ' ' + @tmpstr)
else
set @str = ltrim(@str + ' ' + @tmpstr + ' ' +
rtrim(substring(@unit1 ,(@nbit - 1) * 10 + 1,10)))
if left(@str, 3) = 'And'
set @str = ltrim(right(@str, len(@str) - 3))
if @beforepoint = REPLICATE('0',len(@beforepoint))
break
end
set @beforepoint = replace( @str,'*',' ')
if len(@afterpoint) > 0
set @afterpoint = 'Point' + ' ' + dbo.decodehundred(@afterpoint) + ' '-- + @unit7
else
set @afterpoint = 'Only'
set @numbertostring = replace(@beforepoint,'Zero','') + ' ' + @afterpoint
end
set @numbertostring = ltrim(rtrim(@numbertostring))
if right(@numbertostring,5) = 'point'
set @numbertostring = replace(@numbertostring,'point','')
return @numbertostring
end
go
select dbo.f_numbertostring(12223.01)
/*
------------------------------------
one hundred and twenty-three
（所影响的行数为 1 行）
*/
select dbo.f_numbertostring(100001000)
/*
------------------------------------
twelve thousand three hundred and one
（所影响的行数为 1 行）
*/
drop function decodehundred,f_numbertostring
欢迎转载，转载请注明出处：http://www.cnblogs.com/Tonyyang/
