sql - Extract parts of string separated by delimiter

Question

This is an add on to my original question: Variable length substring between two characters

The data often looks like this, all in one column:

Growth: Compliance;Priority: Contractual;Original Vendor: ABC SERVICES;

In the above example:

• "Compliance" (NOT "Growth: Compliance") needs to be extracted and stored in the GROWTH_TXT column
• "Contractual" needs to be extracted and stored in the PRIORITY_TXT column
• "Original Vendor: ABC Services" can be ignored because it's not stored anywhere

question from:https://stackoverflow.com/questions/65851456/extract-parts-of-string-separated-by-delimiter

Answer 1

SQL Server 2016+

Concept using STRING_SPLIT(), PARSENAME(), PIVOT

-- Mimic Table named z_tbl_tmp
DECLARE @z_tbl_tmp TABLE (id INT, OPTIONAL_FIELD_1 NVARCHAR(max));
INSERT INTO @z_tbl_tmp VALUES (1, N'Growth: Compliance;Priority: Contractual;Original Vendor: ABC SERVICES;');
INSERT INTO @z_tbl_tmp VALUES (2, N'Growth: Run; Priority: Critical - Turns Contractual');
-- 

-- Pivot Parsed Data
WITH tbl_parsed AS (
    -- Parse Data into Key Value Pairs
    SELECT id, 
        TRIM(PARSENAME(REPLACE(value,': ','.'), 2)) AS K, 
        TRIM(PARSENAME(REPLACE(value,': ','.'), 1)) AS V 
    FROM @z_tbl_tmp
        CROSS APPLY STRING_SPLIT(OPTIONAL_FIELD_1,';')
)
SELECT id, [Growth] AS GROWTH_TXT, [Priority] AS PRIORITY_TXT
FROM tbl_parsed
    PIVOT (MAX(V) FOR [K] IN ([Growth], [Priority])) AS pvt

+----+------------+-------------------------------+
| id | GROWTH_TXT | PRIORITY_TXT                  |
+----+------------+-------------------------------+
|  1 | Compliance | Contractual                   |
+----+------------+-------------------------------+
|  2 | Run        | Critical - Turns Contractual  |
+----+------------+-------------------------------+