Extracting-the-filename-from-a-full-path-in-SQL-Se

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

SQL

Copy Code

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

Copy Code

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

Copy Code

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

Shrink ▲   Copy Code

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

Copy Code

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

Nguồn:  https://www.codeproject.com/Tips/866934/Extracting-the-filename-from-a-full-path-in-SQL-Se