Extracting the filename from a full path in SQL Server

• Download Functions.zip - 678 B

Introduction

I store images on disk, and the path to the images in a database table - and for presentation reasons I needed to extract the filename without the extension in SQL, but realised it takes a little thinking about. So rather than do it "in line" in a query, or write an SP to return just the data I wanted, I thought an SQL Function would be useful.

So...SQL functions to break up the path into the bits you might need.

SQL

Four functions (the name is descriptive and the comments provide the detail):

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

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

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

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

Run those on your DB to insert them as scalar functions.

You can then see them under "Programmability...Functions...Scalar-Valued Functions" - you may need to Refresh first by right clicking "Programmability" and selecting "Refresh" from the context menu.

Using the code

Just call the function from your SQL code, remembering to include your schema name in the function when you use it (don't blame me: I didn't write SSMS!) - it will show up in the code as undeclared with a red line (I told you: I didn't write SSMS!) but it'll run fine.

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

Once you close SSMS and reopen it, the function names will no longer be reported as errors, and will appear in Intellisense. (Look, I said I didn't write it - you can't blame me for this.)

Points of Interest

There are times when I hate SSMS...

History

2014-01-18 Original version.

2014-01019 Function name changed from GetPath to GetDirectoryPath to better reflect what it does.