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' */