Phil Parkin - Thursday, April 13, 2017 10:45 AM

If you have the infamous DelimitedSplit8k stashed somewhere, it becomes trivial

DECLARE @FilePath VARCHAR(500) = '\\FileServer\\FileShare\\Directory\Sub Directory\My File Name.pdf';

SELECT TOP 1
   FileName = split.Item
FROM  dbo.udfDelimitedSplit8K(@FilePath, '\')split
ORDER BY split.ItemNumber DESC;