四个函数:
--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