[MSSQL]SQL数字转英文函数

 
-- =============================================
-- 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)
AS
BEGIN
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 @integer=@arabia,@english=''
select @trillion=@integer % 1000000000000000/1000000000000,@billion=@integer % 1000000000000/1000000000,
@million=@integer % 1000000000/1000000,@thousand=(@integer % 1000000)/1000,@hundred=(@integer % 1000)
if @trillion>0
set @english=@english + dbo.ThreeDigit(@trillion) + 'trillion '
if @billion>0
set @english=@english + dbo.ThreeDigit(@billion) + 'billion '
if @million>0
set @english=@english + dbo.ThreeDigit(@million) + 'million '
if @thousand>0
set @english=@english + dbo.ThreeDigit(@thousand) + 'thousand '
if @hundred>0
set @english=@english + dbo.ThreeDigit(@hundred)
if @english=''
set @english='zero '
if @arabia-@integer>0.000000000
begin
declare @decimal decimal(18,17)
select @english=@english+'point ',@decimal=@arabia-@integer
while @decimal>0.0
begin
select @english=@english+e+' ' from @atoe where cast(@decimal*10 as int)=a
set @decimal=@decimal*10-cast(@decimal*10 as int)
end
end
return @english
END
GO
-- =============================================
-- 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 CALLER
AS
BEGIN
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 @integer=@integer % 100
if @integer>0
set @english=@english+'and '
end
if @integer<=20 and @integer>0
select @english=@english+e+' ' from @atoe where @integer=a
if @integer>20
begin
select @english=@english+e+' ' from @atoe where @integer/10*10=a
set @integer=@integer % 10
if @integer>0
select @english=@english+e+' ' from @atoe where @integer=a
end
RETURN @english
END
GO
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 one 
one hundred and twenty trillion forty five billion eighty million forty five thousand fifty four point eight four one two 
zero point zero one zero two five four one 

*/

展开阅读全文

SQL 转ORACLE 函数

07-13

将此SQL函数转为ORACLE的函数或者存储过程rn敬请各位大佬赐教rnrnALTER FUNCTION [dbo].[fn_masterdata_getprocesslistAudit_approver] (@VAPPROVER nvarchar(20))rnRETURNS @returntable table(rniMasterJobId int,rniProcessID nvarchar(40),rniParentProcessID int,rniParentProcessName nvarchar(200),rnvProcessName nvarchar(200),rnvProcessCode nvarchar(100),rntProcessDesc nvarchar(2000),rntprocessobj nvarchar(100),rncflag char(1),rnvCreatedBy nvarchar(200),rndCreatedDt datetime,rnvModifiedBy nvarchar(200),rndModifiedDt datetime,rncMasterStatus nvarchar(200),rnIOwnerDepartment int,rniJoinDepartment int,rnvApprover varchar(20),rnvApproverName nvarchar(200),rnMasterStatus nvarchar(200)rn)rnASrnBEGINrn Declare MyCursor Cursor For select iMasterJobId,cstatus from master_PlanJob where vAPPROVER = @VAPPROVER rn declare @masterJobId intrn declare @cStatus varchar(10)rn Open MyCursorrnFetch next From MyCursor rn Into @masterJobId,@cStatusrnWhile(@@Fetch_Status = 0)rnbeginrn insert into @returntable(iMasterJobId ,iProcessID ,iParentProcessID ,vProcessName ,rnvProcessCode ,tProcessDesc ,tprocessobj ,cflag ,vCreatedBy ,dCreatedDt ,vModifiedBy ,dModifiedDt ,cMasterStatus ,rnIOwnerDepartment ,iJoinDepartment ,vApprover ,masterStatus) select iMasterJobId ,iProcessID ,iParentProcessID ,vProcessName ,rnvProcessCode ,tProcessDesc ,tprocessobj ,cflag ,vCreatedBy ,dCreatedDt ,vModifiedBy ,dModifiedDt ,cMasterStatus ,rnIOwnerDepartment ,iJoinDepartment,@VAPPROVER ,@cStatus from fn_get_masterdata_process_Table(@masterJobId) rnFetch next From MyCursor rn Into @masterJobId,@cStatusrnendrn Close MyCursor rn Deallocate MyCursorrnupdate @returntable set iParentProcessName = (select processName from tbprocess where processID= iParentProcessID)rnupdate @returntable set vApproverName = (select fullname from tbuser where userid = vApprover)rnRETURNrnEND 论坛

没有更多推荐了,返回首页