dwain.c (1/8/2013)

---

Or, even simpler:

WITH Filenames (fn) AS (

SELECT 'XX_YYYYY_AA_BBB_SampleTransaction_120807_55322.TXT'

UNION ALL SELECT 'XX_YYYYYY_DDD_MasterTransaction_120807_00005.TXT')

SELECT fn=LEFT(fn, PATINDEX('%[0-9]%', fn)-2)

FROM Filenames;