Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to fiind data inside of a string Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2008 12:52 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 11:53 AM
Points: 93, Visits: 299
Any help is greatly appreciated. I'm using SQL server 2000 and a have a field that contains a file path like this:

D:\MailRoomImporter\incoming_documents\releases\OC\11000248.pdf

and I'm trying to parse out the directory "releases". The starting position will always be the same, right after: "D:\MailRoomImporter\incoming_documents\" but the directory name and length can change.

So essentially I'm trying to find the field after: "D:\MailRoomImporter\incoming_documents\" and before the next "\"

So far I have:


SELECT FileName,
LTRIM(RTRIM(SUBSTRING(
/* */ FileName,
/* */ CHARINDEX('documents\',FileName,1) +10,
/* */ CHARINDEX('documents\', SUBSTRING(FileName,
CHARINDEX('\', FileName, 1) + 19, 99))

But this is returning some additional characters after releases. Any help is greatly appreciated, thanks!
Post #449632
Posted Wednesday, January 30, 2008 1:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 6,997, Visits: 8,411
So is this what you are looking for ?
declare @myfile varchar(256)
set @myfile='D:\MailRoomImporter\incoming_documents\releases\OC\11000248.pdf'
Select datalength(@myfile)
SELECT @myfile
, substring(@myfile, 40, datalength(@myfile)) as TheRest
, CHARINDEX('\',substring(@myfile, 40, datalength(@myfile)),1) as theslash
, substring( substring(@myfile, 40, datalength(@myfile)), 1, CHARINDEX('\',substring(@myfile, 40, datalength(@myfile)),1) - 1 ) as LookingForThis


LookingForThis results in the text 'releases'


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #449651
Posted Wednesday, January 30, 2008 1:19 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 11:53 AM
Points: 93, Visits: 299
awesome! Thank you. But how do I format this so that I can use it as part of a regular query and use a column name as @myfile?

Thanks again!
Post #449653
Posted Wednesday, January 30, 2008 2:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 11:53 AM
Points: 93, Visits: 299
I figured it out, thanks again!
Post #449696
Posted Wednesday, January 30, 2008 11:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 6,997, Visits: 8,411
Just replace the @var with the column name in the select clause.
I tend to test with @-variables so there is no residue left behind afterwards.

Glad you figured it out ;)



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #449824
Posted Friday, February 01, 2008 10:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:05 PM
Points: 5,472, Visits: 23,551
This might be of use to you
CREATE PROCEDURE Dbo.Parse_Charfieldforavalue_02
@HayStack VARCHAR(200),
@Find VARCHAR(50)
AS
DECLARE @Here Int
DECLARE @There Int
DECLARE @TheNeedle VARCHAR(200)

SET @Here = 0
SET @There = LEN(RTRIM(@HayStack))

SET @Here = PATINDEX('%' + @Find + '%',@HayStack)
/* to make even more flexible replace \ in line below with another input variable */
SET @There = PATINDEX('%\%',SUBSTRING(@HayStack,@Here,@There - @Here))

SET @TheNeedle = ''
SET @TheNeedle = SUBSTRING(@HayStack,@Here,@There -1)
SET @There = @Here + @There -1
/* Adjust to take out the whimsy */
SELECT @TheNeedle AS 'Found ',@Here-1 AS 'Between here', @There AS 'And there' -- Found the needle in the hay stack hurrah

/* Useage:
Parse_Charfieldforavalue_02 'D:\MailRoomImporter\incoming_documents\releases\OC\11000248.pdf','releases' */



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #450637
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse