User Defined Function (UDF) to Get the File Name from a Path in SQL Server

To create a User Defined Function (UDF) that accepts a string with the full path and returns the file name with extension, use the following command:

CREATE FUNCTION ufnFileName (@MyString nvarchar(512))
RETURNS nvarchar(256)
AS
BEGIN
DECLARE @ReturnString nvarchar(256)
SET @ReturnString = RIGHT(@MyString, CHARINDEX('\', REVERSE(@MyString)) - 1)
RETURN @ReturnString
END
GO

To use this user-defined function:

SELECT dbo.ufnFileName ('D:\sql\temp\code.txt')

ufnFileName_path

To see how to Remove the File Name from a Path in SQL Server:
User Defined Function (UDF) to Remove the File Name from a Path in SQL Server