How To Split Separate Strings in 2 Different Columns in SQL Server
0
I have 2 columns of pipe delimited data that I need to break out into rows but the columns must stay together. Here's what my data looks like:
Plan Name: ABC|DEF|GHI|JKL
Plan Type: HMO|POS|HMO|PPO
I need to end up with 4 rows that look like this:
1 - ABC HMO
2 - DEF POS
3 - GHI HMO
4 - JKL PPO
I know how to separate each column individually using the STUFF function but how do I keep the first value from column 1 with the first value from column 2, etc? Don't know where to start. Appreciate any help!
p.s. - I am not on SQL Server 2016 so can't use STRING_SPLIT
sql
sql-server edited Nov 11 at 17:48
Gordon Linoff
758k35291399
asked Nov 11 at 17:40
shdavis701
1
add a comment |
0
I have 2 columns of pipe delimited data that I need to break out into rows but the columns must stay together. Here's what my data looks like:
Plan Name: ABC|DEF|GHI|JKL
Plan Type: HMO|POS|HMO|PPO
I need to end up with 4 rows that look like this:
1 - ABC HMO
2 - DEF POS
3 - GHI HMO
4 - JKL PPO
I know how to separate each column individually using the STUFF function but how do I keep the first value from column 1 with the first value from column 2, etc? Don't know where to start. Appreciate any help!
p.s. - I am not on SQL Server 2016 so can't use STRING_SPLIT
sql
sql-server edited Nov 11 at 17:48
Gordon Linoff
758k35291399
asked Nov 11 at 17:40
shdavis701
1
add a comment |
0
0
0
I have 2 columns of pipe delimited data that I need to break out into rows but the columns must stay together. Here's what my data looks like:
Plan Name: ABC|DEF|GHI|JKL
Plan Type: HMO|POS|HMO|PPO
I need to end up with 4 rows that look like this:
1 - ABC HMO
2 - DEF POS
3 - GHI HMO
4 - JKL PPO
I know how to separate each column individually using the STUFF function but how do I keep the first value from column 1 with the first value from column 2, etc? Don't know where to start. Appreciate any help!
p.s. - I am not on SQL Server 2016 so can't use STRING_SPLIT
sql
sql-server edited Nov 11 at 17:48
Gordon Linoff
758k35291399
asked Nov 11 at 17:40
shdavis701
1
I have 2 columns of pipe delimited data that I need to break out into rows but the columns must stay together. Here's what my data looks like:
Plan Name: ABC|DEF|GHI|JKL
Plan Type: HMO|POS|HMO|PPO
I need to end up with 4 rows that look like this:
1 - ABC HMO
2 - DEF POS
3 - GHI HMO
4 - JKL PPO
I know how to separate each column individually using the STUFF function but how do I keep the first value from column 1 with the first value from column 2, etc? Don't know where to start. Appreciate any help!
p.s. - I am not on SQL Server 2016 so can't use STRING_SPLIT
sql
sql-server sql
sql-server edited Nov 11 at 17:48
Gordon Linoff
758k35291399
asked Nov 11 at 17:40
shdavis701
1
edited Nov 11 at 17:48
Gordon Linoff
758k35291399
asked Nov 11 at 17:40
shdavis701
1
edited Nov 11 at 17:48
Gordon Linoff
758k35291399
edited Nov 11 at 17:48
Gordon Linoff
758k35291399
edited Nov 11 at 17:48
Gordon Linoff
758k35291399
758k35291399
asked Nov 11 at 17:40
shdavis701
1
asked Nov 11 at 17:40
shdavis701
1
asked Nov 11 at 17:40
shdavis701
1
1
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
1
One method is a recursive CTE:
with t as (
select *
from (values ('ABC|DEF|GHI|JKL', 'HMO|POS|HMO|PPO')) v(plannames, plantypes)
),
cte as (
select convert(varchar(max), left(plannames, charindex('|', plannames + '|') - 1)) as planname,
convert(varchar(max), left(plantypes, charindex('|', plantypes + '|') - 1)) as plantype,
convert(varchar(max), stuff(plannames, 1, charindex('|', plannames + '|'), '')) as planname_rest,
convert(varchar(max), stuff(plantypes, 1, charindex('|', plantypes + '|'), '')) as plantype_rest,
1 as lev
from t
union all
select convert(varchar(max), left(planname_rest, charindex('|', planname_rest + '|') - 1)) as planname,
convert(varchar(max), left(plantype_rest, charindex('|', plantype_rest + '|') - 1)) as plantype,
convert(varchar(max), stuff(planname_rest, 1, charindex('|', planname_rest + '|'), '')) as planname_rest,
convert(varchar(max), stuff(plantype_rest, 1, charindex('|', plantype_rest + '|'), '')) as plantype_rest,
lev + 1
from cte
where planname_rest <> ''
)
select *
from cte;
Here is a db<>fiddle.
answered Nov 11 at 17:54
Gordon Linoff
758k35291399
Will this work for rows that have different numbers of plans/types, not just 4?
– shdavis701
Nov 11 at 18:00
I'm not sure this is returning the expected results in the first place.
– Larnu
Nov 11 at 18:12
The above worked, and I've used it with my real data now and I think it's working. Lamu why don't you think it's returning the expected results? Just want to make sure I'm not missing something. Thanks.
– shdavis701
Nov 11 at 18:17
Larnu* (it'srnnotm) and the fiddle didn't appear to provide the correct results @shdavis.
– Larnu
Nov 11 at 18:23
I did have to change plannames in the 4th convert statement to plantypes and it worked fine.
– shdavis701
Nov 11 at 23:18
|
show 1 more comment
0
Using delimitedsplit8k_lead you could do:
SELECT CONVERT(varchar(3), itemnumber) + ' - ' + PN.item + ' ' + PT.item
FROM YourTable YT
CROSS APPLY dbo.delimitedsplit8k_lead(YT.PlanName,'|') PN
CROSS APPLY dbo.delimitedsplit8k_lead(YT.PlanType,'|') PT
WHERE PN.ItemNumber = PT.ItemNumber;
This assumes PlanName and PlanType have the same number of elements.
answered Nov 11 at 18:01
Larnu
15.3k41630
They do have the same number of elements in every row.
– shdavis701
Nov 11 at 18:17
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%2f53251432%2fhow-to-split-separate-strings-in-2-different-columns-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
One method is a recursive CTE:
with t as (
select *
from (values ('ABC|DEF|GHI|JKL', 'HMO|POS|HMO|PPO')) v(plannames, plantypes)
),
cte as (
select convert(varchar(max), left(plannames, charindex('|', plannames + '|') - 1)) as planname,
convert(varchar(max), left(plantypes, charindex('|', plantypes + '|') - 1)) as plantype,
convert(varchar(max), stuff(plannames, 1, charindex('|', plannames + '|'), '')) as planname_rest,
convert(varchar(max), stuff(plantypes, 1, charindex('|', plantypes + '|'), '')) as plantype_rest,
1 as lev
from t
union all
select convert(varchar(max), left(planname_rest, charindex('|', planname_rest + '|') - 1)) as planname,
convert(varchar(max), left(plantype_rest, charindex('|', plantype_rest + '|') - 1)) as plantype,
convert(varchar(max), stuff(planname_rest, 1, charindex('|', planname_rest + '|'), '')) as planname_rest,
convert(varchar(max), stuff(plantype_rest, 1, charindex('|', plantype_rest + '|'), '')) as plantype_rest,
lev + 1
from cte
where planname_rest <> ''
)
select *
from cte;
Here is a db<>fiddle.
answered Nov 11 at 17:54
Gordon Linoff
758k35291399
Will this work for rows that have different numbers of plans/types, not just 4?
– shdavis701
Nov 11 at 18:00
I'm not sure this is returning the expected results in the first place.
– Larnu
Nov 11 at 18:12
The above worked, and I've used it with my real data now and I think it's working. Lamu why don't you think it's returning the expected results? Just want to make sure I'm not missing something. Thanks.
– shdavis701
Nov 11 at 18:17
Larnu* (it'srnnotm) and the fiddle didn't appear to provide the correct results @shdavis.
– Larnu
Nov 11 at 18:23
I did have to change plannames in the 4th convert statement to plantypes and it worked fine.
– shdavis701
Nov 11 at 23:18
|
show 1 more comment
1
One method is a recursive CTE:
with t as (
select *
from (values ('ABC|DEF|GHI|JKL', 'HMO|POS|HMO|PPO')) v(plannames, plantypes)
),
cte as (
select convert(varchar(max), left(plannames, charindex('|', plannames + '|') - 1)) as planname,
convert(varchar(max), left(plantypes, charindex('|', plantypes + '|') - 1)) as plantype,
convert(varchar(max), stuff(plannames, 1, charindex('|', plannames + '|'), '')) as planname_rest,
convert(varchar(max), stuff(plantypes, 1, charindex('|', plantypes + '|'), '')) as plantype_rest,
1 as lev
from t
union all
select convert(varchar(max), left(planname_rest, charindex('|', planname_rest + '|') - 1)) as planname,
convert(varchar(max), left(plantype_rest, charindex('|', plantype_rest + '|') - 1)) as plantype,
convert(varchar(max), stuff(planname_rest, 1, charindex('|', planname_rest + '|'), '')) as planname_rest,
convert(varchar(max), stuff(plantype_rest, 1, charindex('|', plantype_rest + '|'), '')) as plantype_rest,
lev + 1
from cte
where planname_rest <> ''
)
select *
from cte;
Here is a db<>fiddle.
answered Nov 11 at 17:54
Gordon Linoff
758k35291399
Will this work for rows that have different numbers of plans/types, not just 4?
– shdavis701
Nov 11 at 18:00
I'm not sure this is returning the expected results in the first place.
– Larnu
Nov 11 at 18:12
The above worked, and I've used it with my real data now and I think it's working. Lamu why don't you think it's returning the expected results? Just want to make sure I'm not missing something. Thanks.
– shdavis701
Nov 11 at 18:17
Larnu* (it'srnnotm) and the fiddle didn't appear to provide the correct results @shdavis.
– Larnu
Nov 11 at 18:23
I did have to change plannames in the 4th convert statement to plantypes and it worked fine.
– shdavis701
Nov 11 at 23:18
|
show 1 more comment
1
1
1
One method is a recursive CTE:
with t as (
select *
from (values ('ABC|DEF|GHI|JKL', 'HMO|POS|HMO|PPO')) v(plannames, plantypes)
),
cte as (
select convert(varchar(max), left(plannames, charindex('|', plannames + '|') - 1)) as planname,
convert(varchar(max), left(plantypes, charindex('|', plantypes + '|') - 1)) as plantype,
convert(varchar(max), stuff(plannames, 1, charindex('|', plannames + '|'), '')) as planname_rest,
convert(varchar(max), stuff(plantypes, 1, charindex('|', plantypes + '|'), '')) as plantype_rest,
1 as lev
from t
union all
select convert(varchar(max), left(planname_rest, charindex('|', planname_rest + '|') - 1)) as planname,
convert(varchar(max), left(plantype_rest, charindex('|', plantype_rest + '|') - 1)) as plantype,
convert(varchar(max), stuff(planname_rest, 1, charindex('|', planname_rest + '|'), '')) as planname_rest,
convert(varchar(max), stuff(plantype_rest, 1, charindex('|', plantype_rest + '|'), '')) as plantype_rest,
lev + 1
from cte
where planname_rest <> ''
)
select *
from cte;
Here is a db<>fiddle.
answered Nov 11 at 17:54
Gordon Linoff
758k35291399
One method is a recursive CTE:
with t as (
select *
from (values ('ABC|DEF|GHI|JKL', 'HMO|POS|HMO|PPO')) v(plannames, plantypes)
),
cte as (
select convert(varchar(max), left(plannames, charindex('|', plannames + '|') - 1)) as planname,
convert(varchar(max), left(plantypes, charindex('|', plantypes + '|') - 1)) as plantype,
convert(varchar(max), stuff(plannames, 1, charindex('|', plannames + '|'), '')) as planname_rest,
convert(varchar(max), stuff(plantypes, 1, charindex('|', plantypes + '|'), '')) as plantype_rest,
1 as lev
from t
union all
select convert(varchar(max), left(planname_rest, charindex('|', planname_rest + '|') - 1)) as planname,
convert(varchar(max), left(plantype_rest, charindex('|', plantype_rest + '|') - 1)) as plantype,
convert(varchar(max), stuff(planname_rest, 1, charindex('|', planname_rest + '|'), '')) as planname_rest,
convert(varchar(max), stuff(plantype_rest, 1, charindex('|', plantype_rest + '|'), '')) as plantype_rest,
lev + 1
from cte
where planname_rest <> ''
)
select *
from cte;
Here is a db<>fiddle.
answered Nov 11 at 17:54
Gordon Linoff
758k35291399
edited Nov 12 at 2:03
answered Nov 11 at 17:54
Gordon Linoff
758k35291399
answered Nov 11 at 17:54
Gordon Linoff
758k35291399
answered Nov 11 at 17:54
Gordon Linoff
758k35291399
758k35291399
Will this work for rows that have different numbers of plans/types, not just 4?
– shdavis701
Nov 11 at 18:00
I'm not sure this is returning the expected results in the first place.
– Larnu
Nov 11 at 18:12
The above worked, and I've used it with my real data now and I think it's working. Lamu why don't you think it's returning the expected results? Just want to make sure I'm not missing something. Thanks.
– shdavis701
Nov 11 at 18:17
Larnu* (it'srnnotm) and the fiddle didn't appear to provide the correct results @shdavis.
– Larnu
Nov 11 at 18:23
I did have to change plannames in the 4th convert statement to plantypes and it worked fine.
– shdavis701
Nov 11 at 23:18
|
show 1 more comment
Will this work for rows that have different numbers of plans/types, not just 4?
– shdavis701
Nov 11 at 18:00
I'm not sure this is returning the expected results in the first place.
– Larnu
Nov 11 at 18:12
The above worked, and I've used it with my real data now and I think it's working. Lamu why don't you think it's returning the expected results? Just want to make sure I'm not missing something. Thanks.
– shdavis701
Nov 11 at 18:17
Larnu* (it'srnnotm) and the fiddle didn't appear to provide the correct results @shdavis.
– Larnu
Nov 11 at 18:23
I did have to change plannames in the 4th convert statement to plantypes and it worked fine.
– shdavis701
Nov 11 at 23:18
Will this work for rows that have different numbers of plans/types, not just 4?
– shdavis701
Nov 11 at 18:00
Will this work for rows that have different numbers of plans/types, not just 4?
– shdavis701
Nov 11 at 18:00
I'm not sure this is returning the expected results in the first place.
– Larnu
Nov 11 at 18:12
I'm not sure this is returning the expected results in the first place.
– Larnu
Nov 11 at 18:12
The above worked, and I've used it with my real data now and I think it's working. Lamu why don't you think it's returning the expected results? Just want to make sure I'm not missing something. Thanks.
– shdavis701
Nov 11 at 18:17
The above worked, and I've used it with my real data now and I think it's working. Lamu why don't you think it's returning the expected results? Just want to make sure I'm not missing something. Thanks.
– shdavis701
Nov 11 at 18:17
Larnu* (it's
rn not m) and the fiddle didn't appear to provide the correct results @shdavis.– Larnu
Nov 11 at 18:23
Larnu* (it's
rn not m) and the fiddle didn't appear to provide the correct results @shdavis.– Larnu
Nov 11 at 18:23
I did have to change plannames in the 4th convert statement to plantypes and it worked fine.
– shdavis701
Nov 11 at 23:18
I did have to change plannames in the 4th convert statement to plantypes and it worked fine.
– shdavis701
Nov 11 at 23:18
|
show 1 more comment
0
Using delimitedsplit8k_lead you could do:
SELECT CONVERT(varchar(3), itemnumber) + ' - ' + PN.item + ' ' + PT.item
FROM YourTable YT
CROSS APPLY dbo.delimitedsplit8k_lead(YT.PlanName,'|') PN
CROSS APPLY dbo.delimitedsplit8k_lead(YT.PlanType,'|') PT
WHERE PN.ItemNumber = PT.ItemNumber;
This assumes PlanName and PlanType have the same number of elements.
answered Nov 11 at 18:01
Larnu
15.3k41630
They do have the same number of elements in every row.
– shdavis701
Nov 11 at 18:17
add a comment |
0
Using delimitedsplit8k_lead you could do:
SELECT CONVERT(varchar(3), itemnumber) + ' - ' + PN.item + ' ' + PT.item
FROM YourTable YT
CROSS APPLY dbo.delimitedsplit8k_lead(YT.PlanName,'|') PN
CROSS APPLY dbo.delimitedsplit8k_lead(YT.PlanType,'|') PT
WHERE PN.ItemNumber = PT.ItemNumber;
This assumes PlanName and PlanType have the same number of elements.
answered Nov 11 at 18:01
Larnu
15.3k41630
They do have the same number of elements in every row.
– shdavis701
Nov 11 at 18:17
add a comment |
0
0
0
Using delimitedsplit8k_lead you could do:
SELECT CONVERT(varchar(3), itemnumber) + ' - ' + PN.item + ' ' + PT.item
FROM YourTable YT
CROSS APPLY dbo.delimitedsplit8k_lead(YT.PlanName,'|') PN
CROSS APPLY dbo.delimitedsplit8k_lead(YT.PlanType,'|') PT
WHERE PN.ItemNumber = PT.ItemNumber;
This assumes PlanName and PlanType have the same number of elements.
answered Nov 11 at 18:01
Larnu
15.3k41630
Using delimitedsplit8k_lead you could do:
SELECT CONVERT(varchar(3), itemnumber) + ' - ' + PN.item + ' ' + PT.item
FROM YourTable YT
CROSS APPLY dbo.delimitedsplit8k_lead(YT.PlanName,'|') PN
CROSS APPLY dbo.delimitedsplit8k_lead(YT.PlanType,'|') PT
WHERE PN.ItemNumber = PT.ItemNumber;
This assumes PlanName and PlanType have the same number of elements.
answered Nov 11 at 18:01
Larnu
15.3k41630
answered Nov 11 at 18:01
Larnu
15.3k41630
answered Nov 11 at 18:01
Larnu
15.3k41630
answered Nov 11 at 18:01
Larnu
15.3k41630
15.3k41630
They do have the same number of elements in every row.
– shdavis701
Nov 11 at 18:17
add a comment |
They do have the same number of elements in every row.
– shdavis701
Nov 11 at 18:17
They do have the same number of elements in every row.
– shdavis701
Nov 11 at 18:17
They do have the same number of elements in every row.
– shdavis701
Nov 11 at 18:17
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53251432%2fhow-to-split-separate-strings-in-2-different-columns-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
