locked
Splitting a url into it's component parts RRS feed

  • Question

  • Hi,

    Does anyone know how to split a url into it's component parts
    using TSQL?

    I can separate the url into it's root and document library (this is for SharePoint)
    as follows

    DECLARE @url  varchar(100)
    SET @url = 'http://ServerName/sites/MainSite/Subsite1/Subsite2/Shared Documents'

    SELECT
     @url as url,
     LEFT(@url, LEN(@url) - CHARINDEX('/', REVERSE(@url)))as [root],
     RIGHT(@url, CHARINDEX('/', REVERSE(@url))-1) as [doclib]

    But how can I determine and split the subsite names when there is a variable number
    of subsites. Preferably without creating a function?

    I.E
    Server   Main  Sub1  Sub2 etc
    http://ServerName MainSite Subsite1
    http://ServerName MainSite Subsite1 Subsite2

    Thanks

    Tuesday, April 8, 2014 3:40 PM

Answers

  • Hello,

    Please refer to the following T-SQL command to get expected result:

    CREATE TABLE SplitURLTest(string varchar(100))
    
    INSERT INTO SplitURLTest VALUES ('http://ServerName/sites/MainSite/Subsite1/Subsite2/Shared Documents')
    
    ;WITH CTE
    AS
    (
    	SELECT string FROM SplitURLTest
    )
    SELECT string,SUBSTRING(string, 0,  Search2.Pos )
    ,SUBSTRING(string , Search2.Pos , CASE WHEN Search3.Pos>Search2.Pos THEN Search3.pos - Search2.Pos ELSE 0 END)
    ,SUBSTRING(string , Search3.Pos , CASE WHEN Search4.Pos>Search3.Pos THEN Search4.pos - Search3.Pos ELSE 0 END)
    ,SUBSTRING(string , Search4.Pos , CASE WHEN Search5.Pos>Search4.Pos THEN Search5.pos - Search4.Pos ELSE 0 END)
    ,SUBSTRING(string , Search5.Pos , CASE WHEN Search6.Pos>Search5.Pos THEN Search6.pos - Search5.Pos ELSE 0 END)
    
    FROM CTE
    
     CROSS APPLY (SELECT (CHARINDEX('/', string)+1)) AS Search1(Pos)
     CROSS APPLY (SELECT (CHARINDEX('/', string, Search1.Pos+1))) AS Search2(Pos)
     CROSS APPLY (SELECT (CHARINDEX('/', string, Search2.Pos+1))) AS Search3(Pos)
     CROSS APPLY (SELECT (CHARINDEX('/', string, Search3.Pos+1))) AS Search4(Pos)
     CROSS APPLY (SELECT (CHARINDEX('/', string, Search4.Pos+1))) AS Search5(Pos)
     CROSS APPLY (SELECT (CHARINDEX('/', string, Search5.Pos+1))) AS Search6(Pos)
    
    DROP TABLE SplitURLTest

    If you have any feedback on our support, please click here.

    Regards,


    Elvis Long
    TechNet Community Support

    • Edited by Elvis Long Wednesday, April 9, 2014 5:09 AM
    • Marked as answer by Elvis Long Tuesday, April 22, 2014 2:21 AM
    Wednesday, April 9, 2014 3:21 AM

All replies

  • Take a look at this blog post that will help you to solve the problem

    http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/tokenizing-filenames-in-sql-server/


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, April 8, 2014 3:49 PM
  • Hello,

    Please refer to the following T-SQL command to get expected result:

    CREATE TABLE SplitURLTest(string varchar(100))
    
    INSERT INTO SplitURLTest VALUES ('http://ServerName/sites/MainSite/Subsite1/Subsite2/Shared Documents')
    
    ;WITH CTE
    AS
    (
    	SELECT string FROM SplitURLTest
    )
    SELECT string,SUBSTRING(string, 0,  Search2.Pos )
    ,SUBSTRING(string , Search2.Pos , CASE WHEN Search3.Pos>Search2.Pos THEN Search3.pos - Search2.Pos ELSE 0 END)
    ,SUBSTRING(string , Search3.Pos , CASE WHEN Search4.Pos>Search3.Pos THEN Search4.pos - Search3.Pos ELSE 0 END)
    ,SUBSTRING(string , Search4.Pos , CASE WHEN Search5.Pos>Search4.Pos THEN Search5.pos - Search4.Pos ELSE 0 END)
    ,SUBSTRING(string , Search5.Pos , CASE WHEN Search6.Pos>Search5.Pos THEN Search6.pos - Search5.Pos ELSE 0 END)
    
    FROM CTE
    
     CROSS APPLY (SELECT (CHARINDEX('/', string)+1)) AS Search1(Pos)
     CROSS APPLY (SELECT (CHARINDEX('/', string, Search1.Pos+1))) AS Search2(Pos)
     CROSS APPLY (SELECT (CHARINDEX('/', string, Search2.Pos+1))) AS Search3(Pos)
     CROSS APPLY (SELECT (CHARINDEX('/', string, Search3.Pos+1))) AS Search4(Pos)
     CROSS APPLY (SELECT (CHARINDEX('/', string, Search4.Pos+1))) AS Search5(Pos)
     CROSS APPLY (SELECT (CHARINDEX('/', string, Search5.Pos+1))) AS Search6(Pos)
    
    DROP TABLE SplitURLTest

    If you have any feedback on our support, please click here.

    Regards,


    Elvis Long
    TechNet Community Support

    • Edited by Elvis Long Wednesday, April 9, 2014 5:09 AM
    • Marked as answer by Elvis Long Tuesday, April 22, 2014 2:21 AM
    Wednesday, April 9, 2014 3:21 AM
  • see this illustration

    DECLARE @url  varchar(100)
    SET @url = 'http://ServerName/sites/MainSite/Subsite1/Subsite2/Shared Documents'
    
    SELECT [Server],
    MAX(CASE WHEN Seq=1 THEN SiteName END) AS Main,
    MAX(CASE WHEN Seq=2 THEN SiteName END) AS Sub1,
    MAX(CASE WHEN Seq=3 THEN SiteName END) AS Sub2
    FROM
    (
    SELECT LEFT(u,PATINDEX('%[A-Za-z]/[A-Za-z]%',u)) AS [Server],
    m.n.value('.[1]','varchar(100)') AS SiteName,ROW_NUMBER() OVER (PARTITION BY u ORDER BY u) AS Seq
    FROM (SELECT @url as u,CAST('<Root><Row>' + REPLACE(@url,'/','</Row><Row>') + '</Row></Root>' AS xml) AS x) t
    CROSS APPLY x.nodes('/Root/Row[position() > 4]')m(n)
    )r
    GROUP BY [Server]

    to make it dynamic see

    http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, April 9, 2014 6:00 AM
  • ScarePoint,

    Check this:

    http://gallery.technet.microsoft.com/scriptcenter/Script-to-convert-Multiple-7d28b523


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Wednesday, April 9, 2014 6:49 AM