数字转换成英文 金额转成英文大写

/****** 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

　　

转载于:https://www.cnblogs.com/hesijian/p/6592349.html