T-SQL Help needed

[ringo747]

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.

 

[Deanje]

CHARINDEX has an optional parameter of start location.

CHARINDEX(@str, '/', CHARINDEX(@str, '-'))

Like so. So, I'd try something along the lines of:

SUBSTRING(@str, CHARINDEX(@str, '-'), CHARINDEX(@str, '/', CHARINDEX(@str, '-')) - CHARINDEX(@str, '-'))

 

[ringo747]

Will have a look at that. Do you just nest CHARINDEXs together until the 3rd delimiter really?

 

[Deanje]

Not really; if I understand your question correctly.

You're telling CHARINDEX to start at a certain point(in this case, the first occurance of '-'), so you're skipping the first two slashes; it never reads them.

Then, knowing the start and end of the string you want, you've got the length so you can use SUBSTRING to pick it out.

 

[Pho]

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 .

 

[sist_si]

I have a nifty little function I use to return a table from a list of items. It also splits by a chosen seperating string.

CREATE FUNCTION [dbo].[fn_myListToTable](@itemlist nvarchar(MAX), @sep  nvarchar(3))
   RETURNS @tbl TABLE (val nvarchar(255) NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nxtpos    int,
           @len   int

   SELECT @pos = 0, @nxtpos = 1

   WHILE @nxtpos > 0
   BEGIN
      SELECT @nxtpos = charindex(@sep, @list, @pos + 1)
      SELECT @len = CASE WHEN @nxtpos > 0
                         THEN @nxtpos
                         ELSE len(@itemlist) + 1
                         END - @pos - 1
        if not substring(@list, @pos + 1, @len) is null 
            begin
            INSERT @tbl (val)
            VALUES (ltrim(rtrim(substring(@itemlist, @pos + 1, @len))))
            end
    SELECT @pos = @nxtpos
   END
  RETURN
END

So, you could say SELECT * FROM dbo.fn_myListToTable('part1/part2/part3 - part4/part5', '/');

 

[ringo747]

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 .

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!

 

[ringo747]

Ignore - got it sorted