This should handle pretty much anything you can throw at it.
-- DROP TABLE #FileName
--===== Create a test table with some normal and "odd" file/path names.
SELECT FileName = 'C:/Location/Folder/Subfolder/filename20130101.xxx'
INTO #FileName UNION ALL
SELECT FileName = 'C:/Location/Folder/Subfolder/filename20130102' UNION ALL
SELECT FileName = 'filename20130103' UNION ALL
SELECT FileName = 'filename20130104.xxx' UNION ALL
SELECT FileName = '20130105.xxx' UNION ALL
SELECT FileName = '20130106' UNION ALL
SELECT FileName = '20130107def' UNION ALL
SELECT FileName = 'abc20130108def' UNION ALL
SELECT FileName = 'abc20130109def.xxx' UNION ALL
SELECT FileName = '123abc20130110def.xxx' UNION ALL
SELECT FileName = 'abcdef.xxx' --will not show up because has no numbers
;
--===== Get just the last set of numbers.
SELECT fn.FileName, DateOnly = LEFT(FromTheNumbers,ISNULL(NULLIF(PATINDEX('%[0-9][^0-9]%',FromTheNumbers),0),8000))
FROM #FileName fn
CROSS APPLY (SELECT RIGHT(FileName,ISNULL(NULLIF(PATINDEX('%[0-9][^0-9]%',REVERSE(FileName)),0),8000))) ca (FromTheNumbers)
WHERE fn.FileName LIKE '%[0-9]%'
;
Results:
FileName DateOnly
------------------------------------------------- --------
C:/Location/Folder/Subfolder/filename20130101.xxx 20130101
C:/Location/Folder/Subfolder/filename20130102 20130102
filename20130103 20130103
filename20130104.xxx 20130104
20130105.xxx 20130105
20130106 20130106
20130107def 20130107
abc20130108def 20130108
abc20130109def.xxx 20130109
123abc20130109def.xxx 20130110
(10 row(s) affected)
--Jeff Moden
Change is inevitable... Change for the better is not.