/****** Object: UserDefinedFunction [dbo].[NumberToStr] Script Date: 03/20/2017 23:38:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--创建函数
ALTER FUNCTION [dbo].[NumberToStr]
(
@ad_input decimal(38,17)
)
RETURNS nvarchar(max)
AS
BEGIN
declare @ltd_Digits table(Number int,[Str] nvarchar(10))
insert into @ltd_Digits select 0,'zero' union all select 1,'one'
union all select 2,'two' union all select 3,'three'
union all select 4,'four' union all select 5,'five'
union all select 6,'six' union all select 7,'seven'
union all select 8,'eight' union all select 9,'nine'
declare @lbi_Num bigint;
declare @li_Trillion int;
declare @li_Billion int;
declare @li_Million int
declare @li_Thousand int
declare @li_Hundred int
declare @ls_result nvarchar(max)
declare @lb_Minus nvarchar(10)
set @ls_result=''
if @ad_input<0
begin
set @lb_Minus = 'Minus '
set @ad_input = right(convert(nvarchar,@ad_input),len(@ad_input)-1)
end
select @lbi_Num=@ad_input
select @li_Trillion=@lbi_Num % 1000000000000000/1000000000000,@li_Billion=@lbi_Num % 1000000000000/1000000000,
@li_Million=@lbi_Num % 1000000000/1000000,@li_Thousand=(@lbi_Num % 1000000)/1000,@li_Hundred=(@lbi_Num % 1000)
if @li_Trillion>0
set @ls_result=@ls_result + dbo.[Fn_Sys_ThreeDigit](@li_Trillion) + 'trillion '
if @li_Billion>0
set @ls_result=@ls_result + dbo.[Fn_Sys_ThreeDigit](@li_Billion) + 'billion '
if @li_Million>0
set @ls_result=@ls_result + dbo.[Fn_Sys_ThreeDigit](@li_Million) + 'million '
if @li_Thousand>0
set @ls_result=@ls_result + dbo.[Fn_Sys_ThreeDigit](@li_Thousand) + 'thousand '
if @li_Hundred>0
set @ls_result=@ls_result + dbo.[Fn_Sys_ThreeDigit](@li_Hundred)
if @ls_result=''
set @ls_result='zero '
if @ad_input-@lbi_Num>0.000000000
begin
declare @ld_temp decimal(18,17)
select @ls_result=@ls_result+'point ',@ld_temp=@ad_input-@lbi_Num
while @ld_temp>0.0
begin
select @ls_result=@ls_result+[Str]+' ' from @ltd_Digits where cast(@ld_temp*10 as int)=Number
set @ld_temp=@ld_temp*10-cast(@ld_temp*10 as int)
end
end
return @lb_Minus+@ls_result + 'only'
END
GO
/****** Object: UserDefinedFunction [dbo].[Fn_Sys_ThreeDigit] Script Date: 03/20/2017 23:40:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Fn_Sys_ThreeDigit]
(
@ai_Num int
)
RETURNS varchar(100)
WITH EXECUTE AS CALLER
AS
BEGIN
declare @ltd_Item table(Number int,[Str] varchar(10))
insert into @ltd_Item select 0,'zero' union all select 1,'one'
union all select 2,'two' union all select 3,'three'
union all select 4,'four' union all select 5,'five'
union all select 6,'six' union all select 7,'seven'
union all select 8,'eight' union all select 9,'nine'
union all select 10,'ten' union all select 11,'eleven'
union all select 12,'twelve' union all select 13,'thirteen'
union all select 14,'fourteen' union all select 15,'fifteen'
union all select 16,'sixteen' union all select 17,'seventeen'
union all select 18,'eighteen' union all select 19,'nineteen'
union all select 20,'twenty' union all select 30,'thirty'
union all select 40,'forty' union all select 50,'fifty'
union all select 60,'sixty' union all select 70,'severty'
union all select 80,'eighty' union all select 90,'ninety'
declare @ls_English varchar(100)
set @ls_English=''
if @ai_Num>99
begin
select @ls_English=[Str]+' hundred ' from @ltd_Item where @ai_Num/100=Number
set @ai_Num=@ai_Num % 100
if @ai_Num>0
set @ls_English=@ls_English+'and '
end
if @ai_Num<=20 and @ai_Num>0
select @ls_English=@ls_English+[Str]+' ' from @ltd_Item where @ai_Num=Number
if @ai_Num>20
begin
select @ls_English=@ls_English+[Str]+' ' from @ltd_Item where @ai_Num/10*10=Number
set @ai_Num=@ai_Num % 10
if @ai_Num>0
select @ls_English=@ls_English+[Str]+' ' from @ltd_Item where @ai_Num=Number
end
RETURN @ls_English
END
GO
