dwain.c (1/8/2013)

---

Here are two ways:

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=SUBSTRING(fn, 1, CHARINDEX('.', fn))

FROM Filenames;

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=Item

FROM Filenames

CROSS APPLY PatternSplitCM(fn, '[0-9a-zA-Z_]')

WHERE [Matched] = 1 AND ItemNumber = 1;

The second uses PatternSplitCM, which is a general tool that can be used for this purpose, and is described in the 4th article in my signature (splitting strings on patterns). That article also shows how to split out other components of a file name.