关闭

在 SQL Server 中从完整路径提取文件名(sql 玩转文件路径)

标签: sql文件名文件路径扩展名
1714人阅读 评论(0) 收藏 举报
分类:

四个函数:

--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、根据路径获取路径目录(无文件名)

-- =============================================
-- 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



转载出处:http://www.codeforge.cn/article/249284



0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:33055次
    • 积分:485
    • 等级:
    • 排名:千里之外
    • 原创:17篇
    • 转载:10篇
    • 译文:0篇
    • 评论:5条
    文章分类
    最新评论