Início / dba / Perguntas / 91401
Accepted
Musakkhir Sayyed
Asked: 2015-02-08 22:44:35 +0800 CST

Como dividir url no servidor sql como este

  • 772

aqui está a url

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

quero resultado assim

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/

Por favor, olhe para este código ..

            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
sql-server sql-server-2008

2 respostas

  1. Aqui está um que é fortemente baseado no DelimitedSplit8k de Jeff Moden:

    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

    Isso deve superar qualquer loop/código recursivo se usado como uma função.

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

    • 6
  2. Best Answer

    Eu tenho solução alternativa para o mesmo.

    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
    • 2

relate perguntas