Selecting part of a variable length string

Question

Selecting part of a variable length string

Ed Phillips

SSChasing Mays

Points: 634
More actions
May 23, 2005 at 9:07 am

#67705

I have a query which selects file paths returning a string such as '\\server\test\folder1\do1.doc' and 'p:\test\folder10\doc20.doc'
The common part of the string is the folder 'test' and I need to return the part of the string between the \ following test and the next \ ie folder1 or folder 10.
Could someone point me in the right direction for the best way to manipulate this string using T-SQL and what inbuilt functions would be best suited to this task.
Many thanks.

Ed Phillips

Viewing 5 posts - 1 through 4 (of 4 total)

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

Steve Jones - SSC Editor SSC Guru Points: 735611 More actions · Answer 1

charindex and/or patindex. Use them to find test (numericl position), then get the numerical position of the next \ wher you want to stop. Use patindex again, but a starting position > 1 and then substring to grab the middle.

http://www.sqlservercentral.com/columnists/sjones/20010424135929_1.asp

http://www.sqlservercentral.com/columnists/sjones/tamestrings1.asp

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 2

This also works,

CREATE FUNCTION LastIndexOf

(@strValue VARCHAR(4000),

@strChar VARCHAR(50))

RETURNS INT

AS

BEGIN

DECLARE @index INT

SET @index = 0

WHILE CHARINDEX(@strChar, @strValue) > 0

BEGIN

SET @index = @index + CASE WHEN CHARINDEX(@strChar, @strValue) > 1

THEN

(LEN(@strValue) - LEN(SUBSTRING(@strValue,CHARINDEX(@strChar, @strValue) + LEN(@strChar),LEN(@strValue))))

ELSE

1

END

SET @strValue = SUBSTRING(@strValue,CHARINDEX(@strChar, @strValue) + len(@strChar),LEN(@strValue))

END

RETURN @index

END

GO

SELECT SUBSTRING ( '\\server\test\folder1\do1.doc' , (SELECT dbo.LastIndexOf('\\server\test\folder1\do1.doc' ,'test\')+1) , (LEN('\\server\test\folder1\do1.doc')- (SELECT dbo.LastIndexOf('\\server\test\folder1\do1.doc' ,'\'))

))

GO

Thanks

Prasad Bhogadi
www.inforaise.com

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 3

If you just want to use the

select substring('\\server\test\folder1\do1.doc',

charindex ('test\','\\server\test\folder1\do1.doc') + len('test1'),

charindex ('\do1','\\server\test\folder1\do1.doc')-(charindex ('test\','\\server\test\folder1\do1.doc')+len('test1')))

and

select substring('p:\test\folder10\doc20.doc',

charindex ('test\','p:\test\folder10\doc20.doc') + len('test\'),

charindex ('\doc20','p:\test\folder10\doc20.doc')-(charindex ('test\','p:\test\folder10\doc20.doc')+len('test\')))

Will also work if you just want to use the functions implicitly supported in SQL Server

Thanks

Prasad Bhogadi
www.inforaise.com

Ed Phillips SSChasing Mays Points: 634 More actions · Answer 4

Prasad / Steve,

Many thanks, I have now read up on charindex and patindex. Does exactly what I need.

Ed

Ed Phillips