Split string in columns by delimiter using SQL Server

Question

I need to split my one column value in column using delimiter, below is the table structure.

create table #a
(
    id int, 
    timeline varchar(100)
)

insert into #a
values (1, 'Semi Annual Q2 (May/June/July) & Q4 (Nov/Dec/Jan)'),
       (2, 'Semi Annual Q1 (Feb/Mar/Apr) & Q3 (Aug/Sep/Oct)'),
       (3, 'Annual Q3 (Aug/Sep/Oct)'),
       (4, 'Annual Q2 (May/June/July)'),
       (5, 'Annual Q4 (Nov/Dec/Jan)'),
       (6, 'Semi Annual Q1 (Jan/Feb/Mar) & Q3 (July/Aug/Sep)')

select * from #a

Output I want to split timeline values by ' / ' delimiter and make separate column for separate month and all month should be in sequence, which look like a below sample.

ID  M1   M2    M3    M4    M5    M6
---------------------------------------
1   May  June  July  Nov   Dec   Jan
2   Feb  Mar   Apr   Aug   Sep   Oct
3   Aug  Sep   Oct   NULL  NULL  NULL
4   May  June  July  NULL  NULL  NULL
5   Nov  Dec   Jan   NULL  NULL  NULL
6   Jan  Feb   Mar   July  Aug   Sep

So far, I have tried this:

select
    timeline,
    substring((substring(timeline, CHARINDEX('(', timeline) + 1, len(timeline) - 1)), 1, charindex('/', substring(timeline, CHARINDEX('(', timeline) + 1, len(timeline) - 1)) - 1) as M1,
    replace(replace(right(substring(substring(timeline, CHARINDEX('(', timeline) + 1, len(timeline)), 1, charindex(')', substring(timeline, CHARINDEX('(', timeline) + 1, len(timeline)))), charindex('/', reverse(substring(substring(timeline, CHARINDEX('(', timeline) + 1, len(timeline)), 1, charindex(')', substring(timeline, CHARINDEX('(', timeline) + 1, len(timeline))))), 4)), '/', ''), ')', '') as M3
from 
    #a;

which is not a code and too tedious also. please help if you have efficient way to do this.

Jeff Moden's DelimitedSplit8K function should do the trick... You can find it here... sqlservercentral.com/articles/Tally+Table/72993 — Jason A. Long
– Jason A. Long, Commented Sep 25, 2017 at 20:16
I also recommend Jeff's splitter. But even more so I recommend ending the practice of storing delimited data. It violates 1NF and causes lots of anguish. — Sean Lange
– Sean Lange, Commented Sep 25, 2017 at 20:30
Possible duplicate of How to split a comma-separated value to columns — TT.
– TT., Commented Sep 26, 2017 at 6:07

Jason A. Long · Accepted Answer · 2017-09-25 21:36:39Z

2

The SplitCSVToTable8K function being used in the following solution, is the same DelimitedSplit8K function mentioned above...

Here's how to use it in the solution:

WITH 
    cte_ParseTimeline AS (
        SELECT 
            a.id,
            rn = ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY sc.ItemNumber),
            sc.Item
        FROM 
            #a a
            CROSS APPLY dbo.SplitCSVToTable8K(REPLACE(REPLACE(a.timeline, '(', '/'), ')', '/'), '/') sc
        WHERE 
            sc.Item LIKE ('[A-Z][a-z][a-z]')
            OR 
            sc.Item LIKE ('[A-Z][a-z][a-z][a-z]')
        )
SELECT 
    pt.id,
    M1 = MAX(CASE WHEN pt.rn = 1 THEN pt.Item END),
    M2 = MAX(CASE WHEN pt.rn = 2 THEN pt.Item END),
    M3 = MAX(CASE WHEN pt.rn = 3 THEN pt.Item END),
    M4 = MAX(CASE WHEN pt.rn = 4 THEN pt.Item END),
    M5 = MAX(CASE WHEN pt.rn = 5 THEN pt.Item END),
    M6 = MAX(CASE WHEN pt.rn = 6 THEN pt.Item END)
FROM 
    cte_ParseTimeline pt
GROUP BY
    pt.id;

Results...

id          M1    M2    M3    M4    M5    M6
----------- ----- ----- ----- ----- ----- -----
1           May   June  July  Nov   Dec   Jan
2           Feb   Mar   Apr   Aug   Sep   Oct
3           Aug   Sep   Oct   NULL  NULL  NULL
4           May   June  July  NULL  NULL  NULL
5           Nov   Dec   Jan   NULL  NULL  NULL
6           Jan   Feb   Mar   July  Aug   Sep

answered Sep 25, 2017 at 21:36

Jason A. Long

4,4421 gold badge14 silver badges18 bronze badges

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

1 Comment

Jason A. Long Over a year ago

@Jayank - Thank you for the feedback. I'm glad I could hep. :)

Collectives™ on Stack Overflow

Split string in columns by delimiter using SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related