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
  • 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 '15 at 9:38
  • Hi Vérace, Expected result was mentioned above. – Musakkhir Sayyed Jul 14 '15 at 9:59
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/

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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