Extract Data Defined pattern

Question

Post reply

Extract Data Defined pattern

mranganwa

SSC Enthusiast

Points: 125
More actions
January 19, 2010 at 4:24 am

#141819

I have two sitautions.sITUATION 1:
I have imported directory information into a sql server table. Now I want to select everything after <DIR>...
SELECT SUBSTRING(String,CHARINDEX('<DIR>',String)+1,LEN(String)) AS NewCol,String FROM #Test
IF OBJECT_ID('tempdb..#TEST') IS NOT NULL
DROP TABLE #TEST
CREATE TABLE #TEST (
Rowid INT IDENTITY(1,1)
,String VARCHAR(200)
)
INSERT #TEST(String) VALUES('18/03/2009 14:37 <DIR> Fur')
INSERT #TEST(String) VALUES('18/03/2009 14:37 <DIR> Fur')
INSERT #TEST(String) VALUES('18/03/2009 14:40 <DIR> Futurama.Benders.Big.Score.XViD.DVDRiP-ANiVCD')
INSERT #TEST(String) VALUES('21/12/2009 14:25 <DIR> G-Force (2009)')
INSERT #TEST(String) VALUES('14/12/2009 09:54 <DIR> G.I.Joe.The.Rise.Of.Cobra.2009.DvDRip-FxM')
INSERT #TEST(String) VALUES('14/12/2009 11:39 <DIR> gamer HD 1080p (2009)')
INSERT #TEST(String) VALUES('18/03/2009 14:42 <DIR> Garfield')
INSERT #TEST(String) VALUES('18/03/2009 14:47 <DIR> Garfield 2')
INSERT #TEST(String) VALUES('14/12/2009 15:25 <DIR> Garfields.pet.force.2009')
INSERT #TEST(String) VALUES('18/03/2009 14:48 <DIR> GatVol')
INSERT #TEST(String) VALUES('18/03/2009 14:49 <DIR> Georgia Rule')
INSERT #TEST(String) VALUES('18/03/2009 14:49 <DIR> Get Rich Or Die Trying')
INSERT #TEST(String) VALUES('16/09/2009 08:32 <DIR> Get Smart - R5 - AnZ')
INSERT #TEST(String) VALUES('18/03/2009 14:51 <DIR> Getting Played')
INSERT #TEST(String) VALUES(' Volume in drive Z is Volume')
INSERT #TEST(String) VALUES(' Volume Serial Number is 4694-B2C7')
INSERT #TEST(String) VALUES('NULL')
INSERT #TEST(String) VALUES(' Directory of Z:\Movies')
INSERT #TEST(String) VALUES('NULL')
INSERT #TEST(String) VALUES('12/01/2010 08:59 <DIR> .')
INSERT #TEST(String) VALUES('12/01/2010 08:59 <DIR> ..')
INSERT #TEST(String) VALUES('17/03/2009 07:28 <DIR> ''13''')
INSERT #TEST(String) VALUES('17/03/2009 07:26 <DIR> 10 Things i hate about you')
INSERT #TEST(String) VALUES('17/03/2009 07:27 <DIR> 10000 BC')
INSERT #TEST(String) VALUES('03/11/2009 10:08 <DIR> 12 Rounds (2009)')
INSERT #TEST(String) VALUES('17/03/2009 07:30 <DIR> 13 going on 30')
INSERT #TEST(String) VALUES('17/03/2009 07:31 <DIR> 16 Blocks - DvDrip - AnZ')
INSERT #TEST(String) VALUES('17/07/2009 15:36 <DIR> 17 Again (2009)')
INSERT #TEST(String) VALUES('28/08/2009 09:52 <DIR> 2 Fast 2 Furious - DVDRip - AnZ')
INSERT #TEST(String) VALUES('17/03/2009 07:31 <DIR> 2 Hitmen')
INSERT #TEST(String) VALUES('11/12/2009 12:54 <DIR> 2012 Doomsday')
INSERT #TEST(String) VALUES('18/03/2009 18:32 <DIR> Lilo and Stitch')
INSERT #TEST(String) VALUES('18/03/2009 18:35 <DIR> Lion King')
INSERT #TEST(String) VALUES('06/10/2008 12:22 24 List.bat')
INSERT #TEST(String) VALUES('18/03/2009 18:37 <DIR> Little Britain Live 2006')
INSERT #TEST(String) VALUES('18/03/2009 18:39 <DIR> Little Children')
INSERT #TEST(String) VALUES('10/11/2009 14:25 <DIR> Mouse Hunt')
INSERT #TEST(String) VALUES('11/01/2010 17:35 597,310 Movies.txt')
INSERT #TEST(String) VALUES('18/03/2009 20:06 <DIR> Mr Woodcock')
INSERT #TEST(String) VALUES('18/03/2009 22:17 <DIR> Ratatouille[2007]DvDrip[Eng]-aXXo')
INSERT #TEST(String) VALUES('06/01/2010 19:39 79 Real contributions are in AVI.txt')
INSERT #TEST(String) VALUES('18/03/2009 22:23 <DIR> Real Eve')
INSERT #TEST(String) VALUES('23/10/2009 14:46 <DIR> Rebound - R5')
INSERT #TEST(String) VALUES(' Directory of Z:\Movies\Burn After Reading - R5 - AnZ')
INSERT #TEST(String) VALUES('NULL')
INSERT #TEST(String) VALUES('17/03/2009 18:37 <DIR> .')
INSERT #TEST(String) VALUES('17/03/2009 18:37 <DIR> ..')
INSERT #TEST(String) VALUES('25/10/2008 13:36 735,891,456 Burn.After.Reading.2008.R5.XViD-PUKKA_urmanon.avi')
INSERT #TEST(String) VALUES(' 1 File(s) 735,891,456 bytes')
INSERT #TEST(String) VALUES('NULL')
INSERT #TEST(String) VALUES(' Directory of Z:\Movies\Burn-E - DvDRip - AnZ')
INSERT #TEST(String) VALUES('NULL')
INSERT #TEST(String) VALUES('17/03/2009 18:37 <DIR> .')
INSERT #TEST(String) VALUES('17/03/2009 18:37 <DIR> ..')
INSERT #TEST(String) VALUES('12/11/2008 16:27 366,873,791 voa-burn_e_x264_720p_bluray.mkv')
INSERT #TEST(String) VALUES(' 1 File(s) 366,873,791 bytes')
INSERT #TEST(String) VALUES('NULL')
INSERT #TEST(String) VALUES(' Directory of Z:\Movies\ButterFly Effect 2')
INSERT #TEST(String) VALUES('NULL')
INSERT #TEST(String) VALUES('17/03/2009 18:39 <DIR> .')
INSERT #TEST(String) VALUES('17/03/2009 18:39 <DIR> ..')
INSERT #TEST(String) VALUES('30/04/2008 10:32 6,526 The Butterfly Effect 2.jpg')
INSERT #TEST(String) VALUES('05/10/2006 19:21 734,990,336 The.Butterfly.Effect.2.2006.STV.DVDRip.XviD-SAPHiRE.avi')
INSERT #TEST(String) VALUES(' 2 File(s) 734,996,862 bytes')
INSERT #TEST(String) VALUES('NULL')
INSERT #TEST(String) VALUES(' Directory of Z:\Movies\Butterfly Effect 3 - Revelation')
INSERT #TEST(String) VALUES('NULL')
INSERT #TEST(String) VALUES('14/09/2009 13:30 <DIR> .')
INSERT #TEST(String) VALUES('14/09/2009 13:30 <DIR> ..')
INSERT #TEST(String) VALUES('22/03/2009 22:39 734,175,232 Butterfly Effect 3 - Revelation.avi')
INSERT #TEST(String)VALUES(' 1 File(s) 734,175,232 bytes')
INSERT #TEST(String) VALUES('NULL')
INSERT #TEST(String) VALUES(' Directory of Z:\Movies\Caddyshack DVDRip')
INSERT #TEST(String) VALUES('NULL')
INSERT #TEST(String) VALUES('17/03/2009 18:41 <DIR> .')
INSERT #TEST(String) VALUES('17/03/2009 18:41 <DIR> ..')
INSERT #TEST(String) VALUES('14/01/2009 11:19 839,106,560 Caddyshack[1980]DVDRip.avi')
INSERT #TEST(String) VALUES(' 1 File(s) 839,106,560 bytes')
INSERT #TEST(String) VALUES('NULL')
INSERT #TEST(String) VALUES(' Directory of Z:\Movies\Camp.Rock')
INSERT #TEST(String) VALUES('NULL')
INSERT #TEST(String) VALUES('17/03/2009 18:43 <DIR> .')
INSERT #TEST(String) VALUES('17/03/2009 18:43 <DIR> ..')
INSERT #TEST(String) VALUES('17/07/2008 19:54 732,893,184 Camp.Rock.(2008).HDTV.XviD-hibocbii.avi')
INSERT #TEST(String) VALUES(' 1 File(s) 732,893,184 bytes')
INSERT #TEST(String) VALUES('NULL')
INSERT #TEST(String) VALUES(' Directory of Z:\Movies\Caprica.[2009]')
INSERT #TEST(String) VALUES('NULL')

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

paulm-510034 SSC Enthusiast Points: 136 More actions · Answer 1

I think this should help

SELECT SUBSTRING(String,CHARINDEX('<DIR>',String)+1,LEN(String)) AS NewCol,String ,

CHARINDEX('<DIR>',String) AS POSITION,

CASE

WHEN CHARINDEX('<DIR>',String)>0 THEN

SUBSTRING(String,CHARINDEX('<DIR>',String)+5,LEN(String)) --AS NewCol

ELSE

String

END AS RevisedCol

FROM #Test

Regards,

Paul

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

mranganwa (1/19/2010)
Burn.After.Reading

:w00t:

Some of the recent questions might have been better marked "burn before reading"!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden