Click here to Skip to main content
15,845,565 members
Articles / Database Development / SQL Server / SQL Server 2014
Tip/Trick

Extracting the filename from a full path in SQL Server

Rate me:
Please Sign up or sign in to vote.
4.96/5 (8 votes)
19 Jan 2015CPOL1 min read 79.2K   217   10   13
Sometimes, you want to extract the filename (with or without the extension) from the full path of a file. It's easy in C# and so forth - just use the Path.GetFileName method. But what if you want it in SQL?

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):

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

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
CEO
Wales Wales
Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?

Comments and Discussions

 
Questionthank you!! also why reverse? Pin
Member 1490010327-Jul-20 5:55
Member 1490010327-Jul-20 5:55 
SuggestionI made a few tweaks to your functions Pin
Mark Gambo8-Dec-15 7:57
Mark Gambo8-Dec-15 7:57 
Question[GetDirectoryPath] - Add Rtrim() to remove spaces at the end of passed text Pin
Matt Slay17-Oct-15 13:12
Matt Slay17-Oct-15 13:12 
QuestionVery nice!! Pin
Matt Slay17-Oct-15 12:26
Matt Slay17-Oct-15 12:26 
GeneralNice one... Pin
Kornfeld Eliyahu Peter27-Jan-15 23:04
professionalKornfeld Eliyahu Peter27-Jan-15 23:04 
GeneralGood workaround. Pin
Veeravadhani MN20-Jan-15 8:01
Veeravadhani MN20-Jan-15 8:01 
SuggestionNo need to restart SSMS... Pin
daveynogoodboyo19-Jan-15 10:58
daveynogoodboyo19-Jan-15 10:58 
GeneralRe: No need to restart SSMS... Pin
OriginalGriff19-Jan-15 23:06
mveOriginalGriff19-Jan-15 23:06 
QuestionYou forgot one Pin
Jörgen Andersson18-Jan-15 9:49
professionalJörgen Andersson18-Jan-15 9:49 
AnswerRe: You forgot one Pin
OriginalGriff18-Jan-15 9:58
mveOriginalGriff18-Jan-15 9:58 
GeneralRe: You forgot one Pin
Jörgen Andersson18-Jan-15 10:09
professionalJörgen Andersson18-Jan-15 10:09 
GeneralRe: You forgot one Pin
OriginalGriff18-Jan-15 10:17
mveOriginalGriff18-Jan-15 10:17 
GeneralRe: You forgot one Pin
Jörgen Andersson18-Jan-15 10:22
professionalJörgen Andersson18-Jan-15 10:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.