sql server 取文件名函数 转载

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

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGO

SET QUOTED_IDENTIFIER ONGO

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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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