sqlstud (1/8/2013)

---

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.

Thanks dwain.c

using the first approach, the result is like

XX_YYYYY_AA_BBB_SampleTransaction_120807_55322.

XX_YYYYYY_DDD_MasterTransaction_120807_00005.

But i need the below output

XX_YYYYY_AA_BBB_SampleTransaction

XX_YYYYYY_DDD_MasterTransaction

Please suggest on this..

Regards

SqlStud