sql server 取文件名函数 转载
/****** Object: UserDefinedFunction . Script Date: 2016-12-16 16:54:05 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION .
(
@Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @FileName NVARCHAR(MAX)
DECLARE @ReversedPath NVARCHAR(MAX)
DECLARE @PathLength INT
SET @ReversedPath = REVERSE(@Path)
SELECT @PathLength = CHARINDEX('/', @ReversedPath)
SELECT @FileName = LEFT(@Path, LEN(@Path) - @PathLength)
RETURN @FileName
END
GO
/****** Object:UserDefinedFunction . Script Date: 2016-12-16 16:54:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION .
(
@Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @FileName NVARCHAR(MAX)
DECLARE @ReversedPath NVARCHAR(MAX)
DECLARE @ExtLength INT
SET @ReversedPath = REVERSE(@Path)
SET @FileName = ''
SELECT @ExtLength = CHARINDEX('.', @ReversedPath)
IF (@ExtLength > 0)
BEGIN
SELECT @FileName = RIGHT(@Path, @ExtLength - 1)
END
RETURN @FileName
END
GO
/****** Object:UserDefinedFunction . Script Date: 2016-12-16 16:54:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION .
(
@Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @FileName NVARCHAR(MAX)
DECLARE @ReversedPath NVARCHAR(MAX)
SET @ReversedPath = REVERSE(@Path)
SELECT @FileName = RIGHT(@Path, CHARINDEX('/', @ReversedPath)-1)
RETURN @FileName
END
GO
/****** Object: UserDefinedFunction . Script Date: 2016-12-16 17:32:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION .
(
@Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @FileName NVARCHAR(MAX)
DECLARE @ReversedPath NVARCHAR(MAX)
DECLARE @ExtLength INT
SET @ReversedPath = REVERSE(@Path)
SELECT @ExtLength = CHARINDEX('.', @ReversedPath)
SELECT @FileName = (case when CHARINDEX('/', @ReversedPath)>0 then RIGHT(@Path, CHARINDEX('/', @ReversedPath)-1) else @Path end)
SELECT @FileName = LEFT(@FileName, LEN(@FileName) - @ExtLength)
RETURN @FileName
END
GO
/****** Object:UserDefinedFunction . Script Date: 2016-12-16 16:54:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION . (@sourcedata varchar(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
return isnull(rtrim(ltrim(replace(replace(replace(replace(@sourcedata,char(9),''),char(10),''),char(13),''),'',''))),'')
END
GO
/****** Object:UserDefinedFunction . Script Date: 2016-12-16 16:54:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION .(@Long_str varchar(8000),@split_str varchar(100))
returns @t table(id int,item varchar(20))
as
begin
declare @id int
set @id=1
while(charindex(@split_str,@Long_str) <> 0)
begin
insert @t(id,item) values(@id,substring(@Long_str,1,charindex(@split_str,@Long_str)-1))
set @Long_str = stuff(@Long_str,1,charindex(@split_str,@Long_str), ' ')
set @id=@id+1
end
insert @t(id,item) values (@id,@Long_str)
return
end
GO
页:
[1]