January 30, 2008 at 12:52 pm
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!
January 30, 2008 at 1:12 pm
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
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
January 30, 2008 at 1:19 pm
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!
January 30, 2008 at 2:22 pm
I figured it out, thanks again!
January 30, 2008 at 11:57 pm
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
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
February 1, 2008 at 10:28 am
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' */
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply