create function decodehundred(@hundredstring varchar(300) )returns varchar(1000)asbegin 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)) endendreturn @decodehundredend gocreate function f_numbertostring(@number as numeric(12,2)) returns varchar(1000) asbegindeclare @str varchar(100), @beforepoint varchar(100), @afterpoint varchar(100), @tmpstr varchar(100),@numbertostring varchar(200)declare @point int,@nnumlen intdeclare @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 = ''endelse 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 endset @numbertostring = ltrim(rtrim(@numbertostring))if right(@numbertostring,5) = 'point' set @numbertostring = replace(@numbertostring,'point','')return @numbertostringendgoselect 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
