USE [GeologyArchives]
GO
/****** Object: UserDefinedFunction [dbo].[FstReplace] Script Date: 12/13/2010 10:01:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,CJJ>
-- Create date: <Create Date,2009-01-25 ,>
-- Description: <Description,第一位开始第一次找到的子串替换,其他不变>
-- =============================================
ALTER FUNCTION [dbo].[FstReplace]
(
@p_Str varchar(max),
@p_Find varchar(max),
@p_Rep varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
Declare @findLen int,
@strLen int,
@subStr varchar(max),
@rst varchar(max) select @findLen=len(@p_Find)
select @strLen=len(@p_Str)
if @strLen>=@findLen
begin
if left(@p_Str,@findLen)=@p_Find
begin
select @subStr=substring(@p_Str,@findLen+1,@strLen-@findLen)
select @rst=@p_Rep+@subStr
end
else----不符合要求的不替换
begin
select @rst=@p_Str
end
end
else----不符合要求的不替换
begin
select @rst=@p_Str
end
return @rst
END
这个函数 怎么转到ORACLE中去
GO
/****** Object: UserDefinedFunction [dbo].[FstReplace] Script Date: 12/13/2010 10:01:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,CJJ>
-- Create date: <Create Date,2009-01-25 ,>
-- Description: <Description,第一位开始第一次找到的子串替换,其他不变>
-- =============================================
ALTER FUNCTION [dbo].[FstReplace]
(
@p_Str varchar(max),
@p_Find varchar(max),
@p_Rep varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
Declare @findLen int,
@strLen int,
@subStr varchar(max),
@rst varchar(max) select @findLen=len(@p_Find)
select @strLen=len(@p_Str)
if @strLen>=@findLen
begin
if left(@p_Str,@findLen)=@p_Find
begin
select @subStr=substring(@p_Str,@findLen+1,@strLen-@findLen)
select @rst=@p_Rep+@subStr
end
else----不符合要求的不替换
begin
select @rst=@p_Str
end
end
else----不符合要求的不替换
begin
select @rst=@p_Str
end
return @rst
END
这个函数 怎么转到ORACLE中去
create or replace FUNCTION dbo.FstReplace(p_Str in varchar2,
p_Find in varchar2,
p_Rep in varchar2) RETURNS varchar2 AS
findLen number;
strLen number;
subStr varchar2(4000);
rst varchar2(4000);
begin
select length(p_Find) into findLen from dual;
select length(p_Str) into strLen from dual;
if strLen >= findLen then
if substr(p_Str, 0, length(p_Str) - findLen + 1) = p_Find then
select substring(p_Str, findLen + 1, strLen - findLen)
into subStr
from dual;
select p_Rep + subStr into rst from dual;
else
----不符合要求的不替换
select p_Str into rst from dual;
end if;
else
----不符合要求的不替换
select p_Str into rst from dual;
end if;
return rst;
END
(
p_Str varchar2,
p_Find varchar2,
p_Rep varchar2
) return varchar2
as
c_findLen number;
c_strLen number;
c_subStr varchar2(4000);
c_rst varchar2(4000);
begin
select length(p_Find) into c_findLen from dual;
select length(p_Str) into c_strLen from dual;
if c_strLen>=c_findLen then
if substr(p_Str,1,c_findLen)=p_Find then
select substr(p_Str,c_findLen+1,c_strLen-c_findLen) into c_subStr from dual;
select p_Rep+c_subStr into c_rst from dual;
else
select p_Str into c_rst from dual;
end if;
else
select p_Str into c_rst from dual;
end if;
return c_rst;
end;