Wednesday, July 13, 2011

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:

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

Where A1 contains you file and path name.

No comments:

Post a Comment