sql server 取文件名函數 轉載

/****** Object: UserDefinedFunction [dbo].[GetDirectoryPath] Script Date: 2016-12-16 16:54:05 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetDirectoryPath]
(
@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 [dbo].[GetExtension] Script Date: 2016-12-16 16:54:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetExtension]
(
@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 [dbo].[GetFileName] Script Date: 2016-12-16 16:54:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetFileName]
(
@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 [dbo].[GetFileNameWithoutExtension] Script Date: 2016-12-16 17:32:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetFileNameWithoutExtension]
(
@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 [dbo].[RepEmpty] Script Date: 2016-12-16 16:54:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[RepEmpty] (@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 [dbo].[Split] Script Date: 2016-12-16 16:54:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split](@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