Somashekhar Somashekhar - 1 month ago 6
SQL Question

Split value into rows based on delimiter

Spliting the given input value from table into next row (need to split each exactly after 3rd )

Input



Temp (column/Table1)
TBL101 | PC | 1.00 | COMP101 | CS | 1.00 | TQR101 | CP | 5.00 | TXL101 | PC | 1.00 | SQL101 | PC | 1.00........etc


Output



ID Pack qty (columns/Table2)

TBL101 PC 1.00
COMP101 CS 1.00
TQR101 CP 5.00
TXL101 PC 1.00
SQL101 PC 1.00


I'm using the below code to do this but only for first row it is working

DECLARE @Delimiter VARCHAR(40)
SET @Delimiter = '|'
;WITH CTE AS
(
SELECT
CAST('<M>' + REPLACE(temp, @Delimiter , '</M><M>') + '</M>' AS XML)
AS [ColName XML]
FROM Table1
)
--INSERT INTO Table2
-- (ID,PACK,OrderQty)
SELECT
[ColName XML].value('/M[1]', 'bigint') As [ID],
[ColName XML].value('/M[2]', 'VARCHAR(40)') As [Pack},
[ColName XML].value('/M[3]', 'decimal(18,2)') As [OrderQty]

FROM CTE
GO

TT. TT.
Answer

Update: Version for dynamic number of fields in the string. No explanation here, if you want some ask in comments. Enjoy.

CREATE TABLE #dta(
    r NVARCHAR(4000) NOT NULL
);

INSERT INTO #dta(r)VALUES
    ('TBL101 | PC | 1.00 | COMP101 | CS | 1.00 | TQR101 | CP | 5.00 | TXL101 | PC | 1.00 | SQL101 | PC | 1.00'),
    ('TBL102 | PC | 4.00 | COMP102 | CS | 3.00 | TQR102 | CP | 6.00 | TXL102 | PC | 7.00 | SQL102 | PC | 9.00');

DECLARE @num_fields INT;

SELECT
    @num_fields=MAX(LEN(r) - LEN(REPLACE(r,'|',''))) + 1
FROM
    #dta;

DECLARE @fields_sel NVARCHAR(MAX);

SET @fields_sel=STUFF((
SELECT 
 ',[ColName XML].value(''/M['+CAST((N-1)*3+1 AS VARCHAR)+']'', ''NVARCHAR(40)'') As [ID'+CAST(N AS VARCHAR)+']'+
 ',[ColName XML].value(''/M['+CAST((N-1)*3+2 AS VARCHAR)+']'', ''NVARCHAR(40)'') As [Pack'+CAST(N AS VARCHAR)+']'+
 ',[ColName XML].value(''/M['+CAST((N-1)*3+3 AS VARCHAR)+']'', ''decimal(18,2)'') As [OrderQty'+CAST(N AS VARCHAR)+']'
FROM (
    -- 1000 rows
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
) AS tally(N)
WHERE N<=@num_fields/3
FOR XML PATH('')),1,1,'');

DECLARE @ca_sel NVARCHAR(MAX);

SET @ca_sel=STUFF((
SELECT
    ' UNION ALL SELECT RTRIM(LTRIM([ID'+CAST(N AS VARCHAR)+'])),RTRIM(LTRIM([Pack'+CAST(N AS VARCHAR)+'])),[OrderQty'+CAST(N AS VARCHAR)+']'
FROM (
    -- 1000 rows
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
) AS tally(N)
WHERE N<=@num_fields/3
FOR XML PATH('')),1,LEN(' UNION ALL'),'');

--SELECT @ca_sel;

--SELECT @fields_sel;

DECLARE @Delimiter NVARCHAR(40)
SET @Delimiter = N'|'

DECLARE @sql NVARCHAR(MAX);
SET @sql=N'
;WITH CTE([ColName XML]) AS
(
    SELECT
        CAST(''<M>'' + REPLACE(r, @Delimiter , ''</M><M>'') + ''</M>'' AS XML) AS [ColName XML]
    FROM 
        #dta
), sep_fields AS (
SELECT
    '+@fields_sel+N'
FROM CTE
)
SELECT
    up.*
FROM
    sep_fields
    CROSS APPLY (
        '+@ca_sel+N'
    ) AS up([ID],[Pack],[OrderQty])';

---SELECT @sql;

EXEC sp_executesql @sql, N'@Delimiter NVARCHAR(40)', @Delimiter;

DROP TABLE #dta;

Here's an answer for when you know beforehand how many fields will be present in the string field (eg in your question, 15):

CREATE TABLE #dta(
    r NVARCHAR(4000) NOT NULL
);

INSERT INTO #dta(r)VALUES
    ('TBL101 | PC | 1.00 | COMP101 | CS | 1.00 | TQR101 | CP | 5.00 | TXL101 | PC | 1.00 | SQL101 | PC | 1.00'),
    ('TBL102 | PC | 4.00 | COMP102 | CS | 3.00 | TQR102 | CP | 6.00 | TXL102 | PC | 7.00 | SQL102 | PC | 9.00');

DECLARE @Delimiter NVARCHAR(40)
SET @Delimiter = N'|'
;WITH CTE([ColName XML]) AS
(
    SELECT
        CAST('<M>' + REPLACE(r, @Delimiter , '</M><M>') + '</M>' AS XML) AS [ColName XML]
    FROM 
        #dta
), sep_fields AS (
SELECT
 [ColName XML].value('/M[1]', 'NVARCHAR(40)') As [ID1],
 [ColName XML].value('/M[2]', 'NVARCHAR(40)') As [Pack1],
 [ColName XML].value('/M[3]', 'decimal(18,2)') As [OrderQty1],
 [ColName XML].value('/M[4]', 'NVARCHAR(40)') As [ID2],
 [ColName XML].value('/M[5]', 'NVARCHAR(40)') As [Pack2],
 [ColName XML].value('/M[6]', 'decimal(18,2)') As [OrderQty2],
 [ColName XML].value('/M[7]', 'NVARCHAR(40)') As [ID3],
 [ColName XML].value('/M[8]', 'NVARCHAR(40)') As [Pack3],
 [ColName XML].value('/M[9]', 'decimal(18,2)') As [OrderQty3],
 [ColName XML].value('/M[10]', 'NVARCHAR(40)') As [ID4],
 [ColName XML].value('/M[11]', 'NVARCHAR(40)') As [Pack4],
 [ColName XML].value('/M[12]', 'decimal(18,2)') As [OrderQty4],
 [ColName XML].value('/M[13]', 'NVARCHAR(40)') As [ID5],
 [ColName XML].value('/M[14]', 'NVARCHAR(40)') As [Pack5],
 [ColName XML].value('/M[15]', 'decimal(18,2)') As [OrderQty5]
FROM CTE
)
SELECT
    up.*
FROM
    sep_fields
    CROSS APPLY (
        SELECT [ID1],[Pack1],[OrderQty1]
        UNION ALL
        SELECT [ID2],[Pack2],[OrderQty2]
        UNION ALL
        SELECT [ID3],[Pack3],[OrderQty3]
        UNION ALL
        SELECT [ID4],[Pack4],[OrderQty4]
        UNION ALL
        SELECT [ID5],[Pack5],[OrderQty5]
    ) AS up

DROP TABLE #dta;

Result is:

╔═══════════╦═══════╦═══════════╗
║    ID1    ║ Pack1 ║ OrderQty1 ║
╠═══════════╬═══════╬═══════════╣
║ TBL101    ║  PC   ║ 1.00      ║
║  COMP101  ║  CS   ║ 1.00      ║
║  TQR101   ║  CP   ║ 5.00      ║
║  TXL101   ║  PC   ║ 1.00      ║
║  SQL101   ║  PC   ║ 1.00      ║
║ TBL102    ║  PC   ║ 4.00      ║
║  COMP102  ║  CS   ║ 3.00      ║
║  TQR102   ║  CP   ║ 6.00      ║
║  TXL102   ║  PC   ║ 7.00      ║
║  SQL102   ║  PC   ║ 9.00      ║
╚═══════════╩═══════╩═══════════╝