2

Here is the url

www.abc.com/a/b/c/d/e/f/g/h/i

I want result like this

www.abc.com/a/b/c/d/e/f/g/h/i
www.abc.com/a/b/c/d/e/f/g/h
www.abc.com/a/b/c/d/e/f/g
www.abc.com/a/b/c/d/e/f
www.abc.com/a/b/c/d/e
www.abc.com/a/b/c/d
www.abc.com/a/b/c
www.abc.com/a/b
www.abc.com/a
www.abc.com/

Please look out this code..

            declare @length as int
            declare @Totallength as int
            declare @min as int
            declare @strURL as varchar(max)
            set @strURL='www.abc.com/a/b/c/d/e/f/g/h/i'
            set @min=1
            set @length=(SELECT LEN(@strURL) - LEN(REPLACE(@strURL, '/', '')))
            set @Totallength=(select len(@strURL))
            if(@length>1)
            begin
            while(@min<=@length)
            begin
            --select @strURL=left(@strURL,charindex('/',@strURL)-1)
            SELECT  @strURL = SUBSTRING( @strURL, CHARINDEX('/', @strURL) + 1, @Totallength)
            print @strURL
            set @min=@min+1
            -- print @min
            end
            end
2
  • Just to be clear, you want an arbitrary number of strings to be returned from one URL, depending on the number of / (slashes) in that URL?
    – Vérace
    Jul 14, 2015 at 9:38
  • Hi Vérace, Expected result was mentioned above. Jul 14, 2015 at 9:59

2 Answers 2

6

Here's one which is heavily based on Jeff Moden's DelimitedSplit8k:

declare @url VARCHAR(8000), @delimiter char(1)

set @url = 'www.abc.com/aaa/bb/c/ddd/ee/ff/g/h/iii'
set @delimiter = '/'

;WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (
                 SELECT TOP (ISNULL(DATALENGTH(@url),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
ctePos(N1) AS (
                 SELECT t.N-1 FROM cteTally t WHERE SUBSTRING(@url,t.N,1) = @delimiter
                 union all 
                 select datalength(@url)
                )
select left(@url, N1) + case when ROW_NUMBER() OVER(ORDER BY N1) = 1 then '/' else '' end as URL
from ctePos order by N1 desc

This should outperform any loop / recursive code if used as a function.

DelimitedSplit8k source: http://www.sqlservercentral.com/articles/Tally+Table/72993/

0
2

I got alternate solution for the same.

declare @S nvarchar(100) = 'www.abc.com/a/b/c/d/e/f/g/h/i'

while PATINDEX('%[/]%' , @S) > 0 BEGIN
    SET @S = LEFT (@S,LEN(@S) - PATINDEX('%[/]%' , REVERSE(@S)))
SELECT @S
END
1
  • If you have a lot to split, that's going to be slow and resource intensive.
    – Jeff Moden
    Apr 27, 2023 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.