Extract the file name part from a path in Transact SQL.

DECLARE @fileName as varchar(255)

SET @fileName = 'D:\dir1\dir2\file.txt'
PRINT @fileName -- D:\dir1\dir2\file.txt

-- REVERSE is used to get the last "\" of the string
SET @fileName = RIGHT(@fileName, CHARINDEX('\', REVERSE(@fileName))-1)
PRINT @fileName -- file.txt