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

来源：互联网发布：尚长荣唱的怎么样知乎编辑：程序博客网时间：2024/05/17 03:35

四个函数：

--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)ASBEGIN    DECLARE @FileName NVARCHAR(MAX)    DECLARE @ReversedPath NVARCHAR(MAX)     SET @ReversedPath = REVERSE(@Path)    SELECT @FileName = RIGHT(@Path, CHARINDEX('\', @ReversedPath)-1)     RETURN @FileNameEND

--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)ASBEGIN    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 @FileNameEND

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)ASBEGIN    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 @FileNameEND

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)ASBEGIN    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 @FileNameEND

--代码使用示例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 ExtFROM 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