Extract File Name from File Path

How to extract the file name from a file path where the number of sub folders or name of folder is not fixed. In simple terms you do not know the position where the file name starts.

Solution 1:

DECLARE @fileName as varchar(255)

SET @fileName = 'D:\FolderA\FolderB\FileName.txt'

PRINT @fileName

-- D:\FolderA\FolderB\FileName.txt

 -- REVERSE is used to get the last "\" of the string

SET @fileName = RIGHT(@fileName, CHARINDEX('\', REVERSE(@fileName))-1)

PRINT @fileName

-- FileName.txt

Solution 2:

SELECT REVERSE(SUBSTRING(REVERSE(@fpath), 0, CHARINDEX('\', REVERSE(@fpath), 1)))

Solution 3:

This UDF will extract the file name from a full path name

Function FunctionGetFileName(FullPath As String)

Dim StrFind As String

        Do Until Left(StrFind, 1) = "\"

               iCount = iCount + 1

               StrFind = Right(FullPath, iCount)

                       If iCount = Len(FullPath) Then Exit Do

        Loop

        FunctionGetFileName = Right(StrFind, Len(StrFind) - 1)

End Function

To use this UDF push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function dialog box (Shift+F3).
Enter the function like this.

=FunctionGetFileName(A1)

Where A1 contains you file and path name.