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
sql
sql-server tsql split edited Apr 17 '18 at 14:01
a_horse_with_no_name
293k46447541
asked Apr 17 '18 at 14:01
aabdanaabdan
184
add a comment |
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
sql
sql-server tsql split edited Apr 17 '18 at 14:01
a_horse_with_no_name
293k46447541
asked Apr 17 '18 at 14:01
aabdanaabdan
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
add a comment |
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
sql
sql-server tsql split edited Apr 17 '18 at 14:01
a_horse_with_no_name
293k46447541
asked Apr 17 '18 at 14:01
aabdanaabdan
184
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 sql
sql-server tsql split edited Apr 17 '18 at 14:01
a_horse_with_no_name
293k46447541
asked Apr 17 '18 at 14:01
aabdanaabdan
184
edited Apr 17 '18 at 14:01
a_horse_with_no_name
293k46447541
asked Apr 17 '18 at 14:01
aabdanaabdan
184
edited Apr 17 '18 at 14:01
a_horse_with_no_name
293k46447541
edited Apr 17 '18 at 14:01
a_horse_with_no_name
293k46447541
edited Apr 17 '18 at 14:01
a_horse_with_no_name
293k46447541
293k46447541
asked Apr 17 '18 at 14:01
aabdanaabdan
184
asked Apr 17 '18 at 14:01
aabdanaabdan
184
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
add a comment |
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
add a comment |
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
answered Apr 17 '18 at 14:07
John CappellettiJohn Cappelletti
44.6k62444
add a comment |
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
answered Apr 17 '18 at 14:07
John CappellettiJohn Cappelletti
44.6k62444
add a comment |
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
answered Apr 17 '18 at 14:07
John CappellettiJohn Cappelletti
44.6k62444
add a comment |
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
answered Apr 17 '18 at 14:07
John CappellettiJohn Cappelletti
44.6k62444
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
answered Apr 17 '18 at 14:07
John CappellettiJohn Cappelletti
44.6k62444
answered Apr 17 '18 at 14:07
John CappellettiJohn Cappelletti
44.6k62444
answered Apr 17 '18 at 14:07
John CappellettiJohn Cappelletti
44.6k62444
answered Apr 17 '18 at 14:07
John CappellettiJohn Cappelletti
44.6k62444
44.6k62444
add a comment |
add a comment |
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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