T-SQL Help needed

ringo747

ringo747

ringo747

Soldato
Joined
1 Feb 2006
Posts
8,188
I have a field which contains a URL path something like:

part1/part2/part3 - part4/part5

What I need to do is return only the content of part 4. I can use CHARINDEX to return the position of the hyphen.

Really struggling with this one. How can I return contents between the - and the third '/'?

CHARINDEX only seems to return the first occurrence of a given character.
 
Pho said:
PHP: 
DECLARE @URL TABLE (URL NVARCHAR(MAX))

INSERT @URL
	SELECT 'part1/part2/part3 - part4/part5'

	
SELECT
	CHARINDEX('-', URL)+2 as Hyphen_End_Position,					-- +2 to step over the hyphen and an extra space and ignore them
	LEN(URL)-CHARINDEX('/', REVERSE(URL))+1 as Part_4_End_Position,	-- +1 to step over the / and ignore it
	SUBSTRING(	URL,
				CHARINDEX('-', URL)+2,	-- Start extracting from Hyphen_End_Position
				LEN(URL)-CHARINDEX('/', REVERSE(URL))-CHARINDEX('-', URL)-1	-- Extract (Part_4_End_Position-Hyphen_End_Position-1) characters after the start position (ending just before the 4th /)
				)
	AS Extracted
FROM @URL

The first two columns (Hyphen_End_Position and Part_4_End_Position) can be removed; I left them in purely for demonstration purposes.

Probably horribly inefficient mind :p.
Click to expand...

I think I can work with this one but the problem is that the end path may extend for n amount of times i.e. it may go like:

1/2/3/4 - 5/6/7/8
or
1/2/3/4 -5/6

So we don't really know how many '/' we need to pass over. Very tricky!
 
You must log in or register to reply here.
Back
Top Bottom