parse a column contains a path value in sql server










1














How I can parse a column that contains a URL path value in SQL Server?



The input



sites/System1/DocLib1/Folder1/SubFolder/File.pdf


should return:



Column 1 - Column 2 - Column 3- Column 4- Column 5 - Column 6
sites System1 DocLib1 Folder1 SubFolder File.pdf


path value is different for each row










share|improve this question



















  • 1




    Do you know exactly how many columns you want the result to always have? SQL won't give differing numbers of columns based on the data. Another important question is why would you want to do this? There may be a better approach if we understand how you intend to make use of the results.
    – MatBailie
    Apr 17 '18 at 14:02
















1














How I can parse a column that contains a URL path value in SQL Server?



The input



sites/System1/DocLib1/Folder1/SubFolder/File.pdf


should return:



Column 1 - Column 2 - Column 3- Column 4- Column 5 - Column 6
sites System1 DocLib1 Folder1 SubFolder File.pdf


path value is different for each row










share|improve this question



















  • 1




    Do you know exactly how many columns you want the result to always have? SQL won't give differing numbers of columns based on the data. Another important question is why would you want to do this? There may be a better approach if we understand how you intend to make use of the results.
    – MatBailie
    Apr 17 '18 at 14:02














1












1








1







How I can parse a column that contains a URL path value in SQL Server?



The input



sites/System1/DocLib1/Folder1/SubFolder/File.pdf


should return:



Column 1 - Column 2 - Column 3- Column 4- Column 5 - Column 6
sites System1 DocLib1 Folder1 SubFolder File.pdf


path value is different for each row










share|improve this question















How I can parse a column that contains a URL path value in SQL Server?



The input



sites/System1/DocLib1/Folder1/SubFolder/File.pdf


should return:



Column 1 - Column 2 - Column 3- Column 4- Column 5 - Column 6
sites System1 DocLib1 Folder1 SubFolder File.pdf


path value is different for each row







sql sql-server tsql split






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Apr 17 '18 at 14:01









a_horse_with_no_name

293k46447541




293k46447541










asked Apr 17 '18 at 14:01









aabdanaabdan

184




184







  • 1




    Do you know exactly how many columns you want the result to always have? SQL won't give differing numbers of columns based on the data. Another important question is why would you want to do this? There may be a better approach if we understand how you intend to make use of the results.
    – MatBailie
    Apr 17 '18 at 14:02













  • 1




    Do you know exactly how many columns you want the result to always have? SQL won't give differing numbers of columns based on the data. Another important question is why would you want to do this? There may be a better approach if we understand how you intend to make use of the results.
    – MatBailie
    Apr 17 '18 at 14:02








1




1




Do you know exactly how many columns you want the result to always have? SQL won't give differing numbers of columns based on the data. Another important question is why would you want to do this? There may be a better approach if we understand how you intend to make use of the results.
– MatBailie
Apr 17 '18 at 14:02





Do you know exactly how many columns you want the result to always have? SQL won't give differing numbers of columns based on the data. Another important question is why would you want to do this? There may be a better approach if we understand how you intend to make use of the results.
– MatBailie
Apr 17 '18 at 14:02













1 Answer
1






active

oldest

votes


















3














If you have a known or max number of levels, you can use a little XML.



If unknown, you would have to go dynamic.



Example



Declare @yourtable table (id int,url varchar(500))
Insert Into @yourtable values
(1,'sites/System1/DocLib1/Folder1/SubFolder/File.pdf')

Select A.id
,B.*
From @yourtable A
Cross Apply (
Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
From (Select Cast('<x>' + replace(url,'/','</x><x>')+'</x>' as xml) as xDim) as A
) B


Returns



id Pos1 Pos2 Pos3 Pos4 Pos5 Pos6 Pos7 Pos8 Pos9
1 sites System1 DocLib1 Folder1 SubFolder File.pdf NULL NULL NULL





share|improve this answer




















    Your Answer






    StackExchange.ifUsing("editor", function ()
    StackExchange.using("externalEditor", function ()
    StackExchange.using("snippets", function ()
    StackExchange.snippets.init();
    );
    );
    , "code-snippets");

    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "1"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f49880198%2fparse-a-column-contains-a-path-value-in-sql-server%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    3














    If you have a known or max number of levels, you can use a little XML.



    If unknown, you would have to go dynamic.



    Example



    Declare @yourtable table (id int,url varchar(500))
    Insert Into @yourtable values
    (1,'sites/System1/DocLib1/Folder1/SubFolder/File.pdf')

    Select A.id
    ,B.*
    From @yourtable A
    Cross Apply (
    Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
    ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
    ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
    ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
    ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
    ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
    ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
    ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
    ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
    From (Select Cast('<x>' + replace(url,'/','</x><x>')+'</x>' as xml) as xDim) as A
    ) B


    Returns



    id Pos1 Pos2 Pos3 Pos4 Pos5 Pos6 Pos7 Pos8 Pos9
    1 sites System1 DocLib1 Folder1 SubFolder File.pdf NULL NULL NULL





    share|improve this answer

























      3














      If you have a known or max number of levels, you can use a little XML.



      If unknown, you would have to go dynamic.



      Example



      Declare @yourtable table (id int,url varchar(500))
      Insert Into @yourtable values
      (1,'sites/System1/DocLib1/Folder1/SubFolder/File.pdf')

      Select A.id
      ,B.*
      From @yourtable A
      Cross Apply (
      Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
      ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
      ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
      From (Select Cast('<x>' + replace(url,'/','</x><x>')+'</x>' as xml) as xDim) as A
      ) B


      Returns



      id Pos1 Pos2 Pos3 Pos4 Pos5 Pos6 Pos7 Pos8 Pos9
      1 sites System1 DocLib1 Folder1 SubFolder File.pdf NULL NULL NULL





      share|improve this answer























        3












        3








        3






        If you have a known or max number of levels, you can use a little XML.



        If unknown, you would have to go dynamic.



        Example



        Declare @yourtable table (id int,url varchar(500))
        Insert Into @yourtable values
        (1,'sites/System1/DocLib1/Folder1/SubFolder/File.pdf')

        Select A.id
        ,B.*
        From @yourtable A
        Cross Apply (
        Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
        ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
        ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
        ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
        ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
        ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
        ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
        ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
        ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
        From (Select Cast('<x>' + replace(url,'/','</x><x>')+'</x>' as xml) as xDim) as A
        ) B


        Returns



        id Pos1 Pos2 Pos3 Pos4 Pos5 Pos6 Pos7 Pos8 Pos9
        1 sites System1 DocLib1 Folder1 SubFolder File.pdf NULL NULL NULL





        share|improve this answer












        If you have a known or max number of levels, you can use a little XML.



        If unknown, you would have to go dynamic.



        Example



        Declare @yourtable table (id int,url varchar(500))
        Insert Into @yourtable values
        (1,'sites/System1/DocLib1/Folder1/SubFolder/File.pdf')

        Select A.id
        ,B.*
        From @yourtable A
        Cross Apply (
        Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
        ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
        ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
        ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
        ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
        ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
        ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
        ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
        ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
        From (Select Cast('<x>' + replace(url,'/','</x><x>')+'</x>' as xml) as xDim) as A
        ) B


        Returns



        id Pos1 Pos2 Pos3 Pos4 Pos5 Pos6 Pos7 Pos8 Pos9
        1 sites System1 DocLib1 Folder1 SubFolder File.pdf NULL NULL NULL






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Apr 17 '18 at 14:07









        John CappellettiJohn Cappelletti

        44.6k62444




        44.6k62444



























            draft saved

            draft discarded
















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f49880198%2fparse-a-column-contains-a-path-value-in-sql-server%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            這個網誌中的熱門文章

            How to read a connectionString WITH PROVIDER in .NET Core?

            Node.js Script on GitHub Pages or Amazon S3

            Museum of Modern and Contemporary Art of Trento and Rovereto