SQL - Separating a merge field into separate fields based on delimiters

Question

I have a table with an nvarchar(max) column including a merged text like below:

ID  MyString
61  Team:Finance,Accounting,HR,Country:Global,
62  Country:Germany,
63  Team:Legal,
64  Team:Finance,Accounting,Country:Global,External:Tenants,Partners,
65  External:Vendors,

What I need is to create another table for each item having the Team, Country and External values separated into 3 different columns.

Id   Team                      Country   External
61   Finance,Accounting,HR     Global    NULL
62   NULL                      Germany   NULL
63   Legal                     NULL      NULL
64   Finance,Accounting        Global    Tenants,Partners
65   NULL                      NULL      Vendors

What is the most efficient way to do it? I'm trying to use STRING_SPLIT but couldn't manage it.

Any help would be appreciated.

Answer 1

Please try the following solution.

Data resembles JSON, so we'll compose a proper JSON via few REPLACE() function calls.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT PRIMARY KEY, tokens NVARCHAR(MAX));
INSERT INTO @tbl (ID, tokens) VALUES
(61, 'Team:Finance,Accounting,HR,Country:Global,'),
(62, 'Country:Germany,'),
(63, 'Team:Legal,'),
(64, 'Team:Finance,Accounting,Country:Global,External:Tenants,Partners,'),
(65, 'External:Vendors,');
-- DDL and sample data population, end

SELECT * 
FROM @tbl
CROSS APPLY OPENJSON('{"' + REPLACE(REPLACE(REPLACE(TRIM(',' FROM tokens), ':', '": "')
    ,',Country',  '", "Country')
    ,',External', '", "External') + '"}')
WITH
(
   Team         VARCHAR(100)    '$.Team',
   Country      VARCHAR(100)    '$.Country',
   [External]   VARCHAR(100)    '$.External'
) AS u;

Output

+----+-------------------------------------------------------------------+-----------------------+---------+------------------+
| ID |                              tokens                               |         Team          | Country |     External     |
+----+-------------------------------------------------------------------+-----------------------+---------+------------------+
| 61 | Team:Finance,Accounting,HR,Country:Global,                        | Finance,Accounting,HR | Global  | NULL             |
| 62 | Country:Germany,                                                  | NULL                  | Germany | NULL             |
| 63 | Team:Legal,                                                       | Legal                 | NULL    | NULL             |
| 64 | Team:Finance,Accounting,Country:Global,External:Tenants,Partners, | Finance,Accounting    | Global  | Tenants,Partners |
| 65 | External:Vendors,                                                 | NULL                  | NULL    | Vendors          |
+----+-------------------------------------------------------------------+-----------------------+---------+------------------+