How To Split Separate Strings in 2 Different Columns in SQL Server

Question

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

Gordon Linoff · Accepted Answer · 2018-11-12 02:03:25Z

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.

edited Nov 12, 2018 at 2:03

answered Nov 11, 2018 at 17:54

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

shdavis701 Over a year ago

Will this work for rows that have different numbers of plans/types, not just 4?

Thom A Over a year ago

I'm not sure this is returning the expected results in the first place.

shdavis701 Over a year ago

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.

Thom A Over a year ago

Larnu* (it's rn not m) and the fiddle didn't appear to provide the correct results @shdavis.

shdavis701 Over a year ago

I did have to change plannames in the 4th convert statement to plantypes and it worked fine.

|

Thom A · Accepted Answer · 2018-11-11 18:01:37Z

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, 2018 at 18:01

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

1 Comment

shdavis701 Over a year ago

They do have the same number of elements in every row.

Collectives™ on Stack Overflow

How To Split Separate Strings in 2 Different Columns in SQL Server

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related