InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 10:17:12 GMT20http://www.sqlservercentral.com/Forums/Topic1403997-391-1.aspx[quote][b]Koen Verbeeck (1/8/2013)[/b][hr]You can reverse the string, look for the position of the second underscore and take the substring starting from that position till the end. Reverse the substring back and you have your result.edit: or don't reverse the string and look for the fourth underscore, if the first part keeps the same structure.[/quote]Thanks KoenTue, 08 Jan 2013 03:45:42 GMTsqlstudhttp://www.sqlservercentral.com/Forums/Topic1403997-391-1.aspx[quote][b]dwain.c (1/8/2013)[/b][hr]Or, even simpler:[code="sql"]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;[/code][/quote]Thanks a lot Dwain.Its working...declare @a varchar(100) = 'XX_YYYYYY_DDD_MasterTransaction_120807_00005.TXT'select LEFT(@A, patindex('%_[0-9]%', @a)-1)RegardsSqlStudTue, 08 Jan 2013 03:39:16 GMTsqlstudhttp://www.sqlservercentral.com/Forums/Topic1403997-391-1.aspxOr, even simpler:[code="sql"]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;[/code]Tue, 08 Jan 2013 01:38:15 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1403997-391-1.aspx[quote][b]sqlstud (1/8/2013)[/b][hr][quote][b]dwain.c (1/8/2013)[/b][hr]Here are two ways:[code="sql"]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=ItemFROM FilenamesCROSS APPLY PatternSplitCM(fn, '[0-9a-zA-Z_]')WHERE [Matched] = 1 AND ItemNumber = 1;[/code]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.[/quote]Thanks dwain.cusing 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_SampleTransactionXX_YYYYYY_DDD_MasterTransactionPlease suggest on this..RegardsSqlStud[/quote]Sorry about that! Misread the requirement. Try this:[code="sql"]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, PATINDEX('%[0-9]%', fn)-2)FROM Filenames;[/code]The PatternSplitCM will work too (not as is) but it would need to be changed into something quite a bit more complicated.Tue, 08 Jan 2013 01:32:08 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1403997-391-1.aspx[quote][b]dwain.c (1/8/2013)[/b][hr]Here are two ways:[code="sql"]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=ItemFROM FilenamesCROSS APPLY PatternSplitCM(fn, '[0-9a-zA-Z_]')WHERE [Matched] = 1 AND ItemNumber = 1;[/code]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.[/quote]Thanks dwain.cusing 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_SampleTransactionXX_YYYYYY_DDD_MasterTransactionPlease suggest on this..RegardsSqlStudTue, 08 Jan 2013 01:20:09 GMTsqlstudhttp://www.sqlservercentral.com/Forums/Topic1403997-391-1.aspx[quote][b]Koen Verbeeck (1/8/2013)[/b][hr]You can reverse the string, look for the position of the second underscore and take the substring starting from that position till the end. Reverse the substring back and you have your result.edit: or don't reverse the string and look for the fourth underscore, if the first part keeps the same structure.[/quote]Thanks SScrazy.Can you please send me the query?RegardsSqlStudTue, 08 Jan 2013 01:15:33 GMTsqlstudhttp://www.sqlservercentral.com/Forums/Topic1403997-391-1.aspxHere are two ways:[code="sql"]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=ItemFROM FilenamesCROSS APPLY PatternSplitCM(fn, '[0-9a-zA-Z_]')WHERE [Matched] = 1 AND ItemNumber = 1;[/code]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.Tue, 08 Jan 2013 00:48:03 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1403997-391-1.aspxYou can reverse the string, look for the position of the second underscore and take the substring starting from that position till the end. Reverse the substring back and you have your result.edit: or don't reverse the string and look for the fourth underscore, if the first part keeps the same structure.Tue, 08 Jan 2013 00:36:37 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1403997-391-1.aspxHi,We have the below file names File Name1 = 'XX_YYYYY_AA_BBB_SampleTransaction_120807_55322.TXT'File Name2 = 'XX_YYYYYY_DDD_MasterTransaction_120807_00005.TXT[b]Query:[/b]Need to extract the file names as belowFile Name1 = XX_YYYYY_AA_BBB_SampleTransactionFile Name2 = XX_YYYYYY_DDD_MasterTransactionThe values after the first underscore from right(_55322) will be increasing from file to file, so we cant use the below querySELECT SUBSTRING(@FILENAME,1,(LEN(@FILENAME)-16))Could you please let me know the query to get the correct result?RegardsSqlStudTue, 08 Jan 2013 00:29:14 GMTsqlstud