How to extract the paricular values from a string

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » SQL Server 2008 - General » How to extract the paricular values from a...

Add to briefcase

How to extract the paricular values from a string

Expand / Collapse

Author

Message

Posted Tuesday, January 08, 2013 12:29 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, Visits: 237

Hi,

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

Query:

Need to extract the file names as below

File Name1 = XX_YYYYY_AA_BBB_SampleTransaction
File Name2 = XX_YYYYYY_DDD_MasterTransaction

The values after the first underscore from right(_55322) will be increasing from file to file, so we cant use the below query

SELECT SUBSTRING(@FILENAME,1,(LEN(@FILENAME)-16))

Could you please let me know the query to get the correct result?

Regards
SqlStud

Post #1403997

Posted Tuesday, January 08, 2013 12:36 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 9,376, Visits: 6,472

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.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1404001

Posted Tuesday, January 08, 2013 12:48 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 2,345, Visits: 3,191

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.

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1404006

Posted Tuesday, January 08, 2013 1:15 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, Visits: 237

Koen Verbeeck (1/8/2013)

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.

Thanks SScrazy.

Can you please send me the query?

Regards
SqlStud

Post #1404031

Posted Tuesday, January 08, 2013 1:20 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, Visits: 237

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

Post #1404033

Posted Tuesday, January 08, 2013 1:32 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 2,345, Visits: 3,191

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

Sorry about that! Misread the requirement. Try this:

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;

The PatternSplitCM will work too (not as is) but it would need to be changed into something quite a bit more complicated.

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1404040

Posted Tuesday, January 08, 2013 1:38 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 2,345, Visits: 3,191

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;

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1404044

Posted Tuesday, January 08, 2013 3:39 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, Visits: 237

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;

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)

Regards
SqlStud

Post #1404099

Posted Tuesday, January 08, 2013 3:45 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 3:57 AM
Points: 93, Visits: 237

Koen Verbeeck (1/8/2013)

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.

Thanks Koen

Post #1404103

« Prev Topic | Next Topic »

Add to briefcase

Permissions

Expand / Collapse