SQL數字轉英文函數

標籤：log   des   rip   rom   eve   caller   git   from   highlight   

-- 數字轉英文-- =============================================-- Author:     qianjin036a-- Create date:06/14/2008 02:27:17-- Description:Arabic numerals to English-- =============================================Go--建立函數CREATE FUNCTION Digit2English(    @arabia decimal(38,17))RETURNS varchar(1000)ASBEGIN    declare @atoe table(a int,e varchar(10))    insert into @atoe 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 @integer bigint,@trillion int,@billion int,@million int,@thousand int,@hundred int,@english varchar(1000)     select @[email protected],@english=‘‘    select @[email protected] % 1000000000000000/1000000000000,@[email protected] % 1000000000000/1000000000,        @[email protected] % 1000000000/1000000,@thousand=(@integer % 1000000)/1000,@hundred=(@integer % 1000)    if @trillion>0        set @[email protected] + dbo.ThreeDigit(@trillion) + ‘trillion ‘    if @billion>0        set @[email protected] + dbo.ThreeDigit(@billion) + ‘billion ‘    if @million>0        set @[email protected] + dbo.ThreeDigit(@million) + ‘million ‘    if @thousand>0        set @[email protected] + dbo.ThreeDigit(@thousand) + ‘thousand ‘    if @hundred>0        set @[email protected] + dbo.ThreeDigit(@hundred)    if @english=‘‘        set @english=‘zero ‘    if @[email protected]>0.000000000        begin            declare @decimal decimal(18,17)            select @[email protected]+‘point ‘,@[email protected]@integer            while @decimal>0.0                begin                    select @[email protected]+e+‘ ‘ from @atoe where cast(@decimal*10 as int)=a                    set @[email protected]*10-cast(@decimal*10 as int)                end        end    return @englishENDGO -- =============================================-- Author:      qianjin036a-- Create date: 06/14/2008 02:27:17-- Description: Three Digit Arabic numerals to English-- =============================================CREATE FUNCTION ThreeDigit(    @integer int)RETURNS varchar(100)WITH EXECUTE AS CALLERASBEGIN    declare @atoe table(a int,e varchar(10))    insert into @atoe 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 @english varchar(100)    set @english=‘‘    if @integer>99        begin            select @english=e+‘ hundred ‘ from @atoe where @integer/100=a            set @[email protected] % 100            if @integer>0                set @[email protected]+‘and ‘        end    if @integer<=20 and @integer>0        select @[email protected]+e+‘ ‘ from @atoe where @integer=a    if @integer>20        begin            select @[email protected]+e+‘ ‘ from @atoe where @integer/10*10=a            set @[email protected] % 10            if @integer>0                select @[email protected]+e+‘ ‘ from @atoe where @integer=a        end    RETURN @englishENDGO select dbo.digit2english(123456789987654.321)union all select dbo.digit2english(120045080045054.8412)union all select dbo.digit2english(0.0102541) go/*---------------------------------------------------------------------one hundred and twenty three trillion four hundred and fifty six billion seven hundred and eighty nine million nine hundred and eighty seven thousand six hundred and fifty four point three two oneone hundred and twenty trillion forty five billion eighty million forty five thousand fifty four point eight four one twozero point zero one zero two five four one*/

　　

SQL數字轉英文函數