how to fiind data inside of a string

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » how to fiind data inside of a string

how to fiind data inside of a string

Rate Topic

Display Mode

Topic Options

Author

Message

ericb1

Posted Wednesday, January 30, 2008 12:52 PM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 1:16 PM
Points: 89, Visits: 289

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

ALZDBA

Posted Wednesday, January 30, 2008 1:12 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 6,861, Visits: 8,048

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

     Jul 13

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

- 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" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #449651

ericb1

Posted Wednesday, January 30, 2008 1:19 PM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 1:16 PM
Points: 89, Visits: 289

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

ericb1

Posted Wednesday, January 30, 2008 2:22 PM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 1:16 PM
Points: 89, Visits: 289

I figured it out, thanks again!

Post #449696

ALZDBA

Posted Wednesday, January 30, 2008 11:57 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 6,861, Visits: 8,048

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

     Jul 13

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

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #449824

bitbucket-25253

Posted Friday, February 01, 2008 10:28 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 5,101, Visits: 20,200

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 »

Permissions