SQL Server 中从完整路径提取文件名代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 SQL Server 中从完整路径提取文件名
作者: / 发布于2018/1/24/ 1000
--1、根据路径获取文件名
-- =============================================  
-- Author:        Paul Griffin  
-- Create date:   18 January 2015  
-- Description:   Returns a filename with extension  
--                from a full path:  
--                    D:\Temp\Resources\Images\My.Picture.jpg  
--                ==> My.Picture.jpg  
-- =============================================  
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  



--2、根据路径获取文件名（去掉扩展名）

-- =============================================  
-- Author:        Paul Griffin  
-- Create date:   18 January 2015  
-- Description:   Returns a filename without extension  
--                from a full path:  
--                    D:\Temp\Resources\Images\My.Picture.jpg  
--                ==> My.Picture  
-- =============================================  
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 = RIGHT(@Path, CHARINDEX('\', @ReversedPath)-1)  
    SELECT @FileName = LEFT(@FileName, LEN(@FileName) - @ExtLength)  
    RETURN @FileName  
END  

3、根据路径获取路径目录（无文件名）
[sql] view plain copy
-- =============================================  
-- Author:        Paul Griffin  
-- Create date:   18 January 2015  
-- Description:   Returns the path without the file name  
--                from a full path:  
--                    D:\Temp\Resources\Images\My.Picture.jpg  
--                ==> D:\Temp\Resources\Images  
-- =============================================  
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  

4、获取文件扩展名
[sql] view plain copy
-- =============================================  
-- Author:        Paul Griffin  
-- Create date:   18 January 2015  
-- Description:   Returns the extension only  
--                from a full path:  
--                    D:\Temp\Resources\Images\My.Picture.jpg  
--                ==> jpg  
-- =============================================  
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  

--代码使用示例1
[sql] view plain copy
SELECT Location  
      ,dbo.GetDirectoryPath(Location) AS [Path Only]  
      ,dbo.GetFileName(Location) AS [Full File Name]  
      ,dbo.GetFileNameWithoutExtension(Location) AS [File Name without Ext  
      ,dbo.GetExtension(Location) AS Ext  
FROM Images  

--代码使用示例2
[sql] view plain copy
declare @filePath nvarchar(4000)  
set @filepath='D:\messages\attachrecv\0.abe3b3d58161ccb3de7ab63754bb3f15\abc.doc'  
SELECT dbo.GetDirectoryPath(@filepath) AS [Path Only]  
      ,dbo.GetFileName(@filepath) AS [Full File Name]  
      ,dbo.GetFileNameWithoutExtension(@filepath) AS [File Name without Ext]  
      ,dbo.GetExtension(@filepath) AS Ext  

得到结果：
path only：D:\messages\attachrecv\0.abe3b3d58161ccb3de7ab63754bb3f15
Full File Name：abc.doc
File Name without Ext：abc
Ext：.doc



3、根据路径获取路径目录（无文件名）
-- =============================================  
-- Author:        Paul Griffin  
-- Create date:   18 January 2015  
-- Description:   Returns the path without the file name  
--                from a full path:  
--                    D:\Temp\Resources\Images\My.Picture.jpg  
--                ==> D:\Temp\Resources\Images  
-- =============================================  
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  




4、获取文件扩展名

-- =============================================  
-- Author:        Paul Griffin  
-- Create date:   18 January 2015  
-- Description:   Returns the extension only  
--                from a full path:  
--                    D:\Temp\Resources\Images\My.Picture.jpg  
--                ==> jpg  
-- =============================================  
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  


--代码使用示例1
SELECT Location  
      ,dbo.GetDirectoryPath(Location) AS [Path Only]  
      ,dbo.GetFileName(Location) AS [Full File Name]  
      ,dbo.GetFileNameWithoutExtension(Location) AS [File Name without Ext  
      ,dbo.GetExtension(Location) AS Ext  
FROM Images  


--代码使用示例2
declare @filePath nvarchar(4000)  
set @filepath='D:\messages\attachrecv\0.abe3b3d58161ccb3de7ab63754bb3f15\abc.doc'  
SELECT dbo.GetDirectoryPath(@filepath) AS [Path Only]  
      ,dbo.GetFileName(@filepath) AS [Full File Name]  
      ,dbo.GetFileNameWithoutExtension(@filepath) AS [File Name without Ext]  
      ,dbo.GetExtension(@filepath) AS Ext  
	  
得到结果：
path only：D:\messages\attachrecv\0.abe3b3d58161ccb3de7ab63754bb3f15
Full File Name：abc.doc
File Name without Ext：abc
Ext：.doc
试试其它关键字
同语言下
可能有用的
贡献的其它代码Label