To handle with special character to split in SQL table accordingly

Question

Hi All, Lets say I have sample file as below text file source. First I will load the data from text file to sql table using SSIS,then I would like get final desired output like below in sql table. I'm stuck from B to C as per diagram below. Anyone can help with the SQL code? I attached my SQL version since the method like OPENJSON/string_split only support SQL Server2016 and later. Attached also sample file252405-test.txt.

Answer 1

Hi @jn93 ,

Please try the following solution.
It will work starting from SQL Server 2012 onwards.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (AUTONUM VARCHAR(50), UKEY VARCHAR(50), INSCODE VARCHAR(50), ACTION_TIMESTAMP VARCHAR(50))  
INSERT INTO @tbl (AUTONUM, UKEY, INSCODE, ACTION_TIMESTAMP) VALUES  
('2287250^2287307^2287308','91TMIQUO6123265^91TMIQUO6123265^91TMIQUO6123265','91^91^91','20220817163329^20220817165355^20220817165358'),  
('2287288^2287303^2287304','91TMIQUO6124293^91TMIQUO6124293^91TMIQUO6124293','91^91^91','20220817164753^20220817165318^20220817165322');  
-- DDL and sample data population, end  
  
DECLARE @separator CHAR(1) = '^';  
  
;WITH rs AS  
(  
SELECT *  
FROM @tbl AS t  
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
  REPLACE(AUTONUM, @separator, ']]></r><r><![CDATA[') +   
  ']]></r></root>' AS XML)) AS t1(a)  
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
  REPLACE(UKEY, @separator, ']]></r><r><![CDATA[') +   
  ']]></r></root>' AS XML)) AS t2(b)  
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
  REPLACE(INSCODE, @separator, ']]></r><r><![CDATA[') +   
  ']]></r></root>' AS XML)) AS t3(c)  
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
  REPLACE(ACTION_TIMESTAMP, @separator, ']]></r><r><![CDATA[') +   
  ']]></r></root>' AS XML)) AS t4(d)  
), rs_AUTONUM AS  
(  
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID  
, t.c.value('(./text())[1]', 'VARCHAR(50)') AS AUTONUM  
FROM rs CROSS APPLY a.nodes('/root/r') AS t(c)  
)  
, rs_UKEY AS  
(  
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID  
, t.c.value('(./text())[1]', 'VARCHAR(50)') AS UKEY  
FROM rs CROSS APPLY b.nodes('/root/r') AS t(c)  
), rs_INSCODE AS  
(  
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID  
, t.c.value('(./text())[1]', 'VARCHAR(50)') AS INSCODE  
FROM rs CROSS APPLY c.nodes('/root/r') AS t(c)  
), rs_ACTION_TIMESTAMP AS  
(  
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID  
, t.c.value('(./text())[1]', 'VARCHAR(50)') AS ACTION_TIMESTAMP  
FROM rs CROSS APPLY d.nodes('/root/r') AS t(c)  
)  
SELECT x1.*, UKEY, INSCODE, ACTION_TIMESTAMP  
FROM rs_AUTONUM AS x1 INNER JOIN rs_UKEY AS x2 ON x1.ID = x2.ID  
INNER JOIN rs_INSCODE AS x3 ON x2.ID = x3.ID  
INNER JOIN rs_ACTION_TIMESTAMP AS x4 ON x3.ID = x4.ID;  

Output

+----+---------+-----------------+---------+------------------+  
| ID | AUTONUM |      UKEY       | INSCODE | ACTION_TIMESTAMP |  
+----+---------+-----------------+---------+------------------+  
|  1 | 2287250 | 91TMIQUO6123265 |      91 |   20220817163329 |  
|  2 | 2287307 | 91TMIQUO6123265 |      91 |   20220817165355 |  
|  3 | 2287308 | 91TMIQUO6123265 |      91 |   20220817165358 |  
|  4 | 2287288 | 91TMIQUO6124293 |      91 |   20220817164753 |  
|  5 | 2287303 | 91TMIQUO6124293 |      91 |   20220817165318 |  
|  6 | 2287304 | 91TMIQUO6124293 |      91 |   20220817165322 |  
+----+---------+-----------------+---------+------------------+  

Answer 2

And what's the logic for the result?
In B I see 2 rows, with each 3 columns containing each 3 separated values = 54 combination; in your result you have only 6 !?!

Answer 3

SELECT distinct   
d1.value AUTONUM  
, d2.value INSCODE  
,d3.value UKEY  
  FROM [dbo].[StageTable]  
  cross apply string_split(ACTION_TIMESTAMP ,'^') d1  
  cross apply string_split(INSCODE,'^') d2  
cross apply string_split(UKEY,'^') d3  

Answer 4

;WITH Num1 (n) AS (  
SELECT 1 as n  
UNION ALL SELECT n+1 as n  
FROM Num1 Where n <101),  
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),  
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)  
,cteAUTONUM as (  
SELECT n, UKEY,   
--ACTION_TIMESTAMP ,  
Substring(AUTONUM , n, charindex('^', AUTONUM  + '^', n) - n)  vals  
,ROW_NUMBER() Over(partition by UKEY Order by n) rn  
FROM [dbo].Stagetable  
Cross apply (Select n from nums) d(n)   
Where n <= len(AUTONUM)   
AND substring('^' + AUTONUM, n, 1) = '^'  
)  
,cteACTION_TIMESTAMP as (  
SELECT n, UKEY,   
--ACTION_TIMESTAMP ,  
Substring(ACTION_TIMESTAMP , n, charindex('^', ACTION_TIMESTAMP  + '^', n) - n)  vals  
,ROW_NUMBER() Over(partition by UKEY Order by n) rn  
FROM [dbo].Stagetable  
Cross apply (Select n from nums) d(n)   
Where n <= len(ACTION_TIMESTAMP)   
AND substring('^' + ACTION_TIMESTAMP, n, 1) = '^'  
)  
,cteINSCODE as (  
SELECT n, UKEY,   
Substring(INSCODE , n, charindex('^', INSCODE  + '^', n) - n)  vals  
,ROW_NUMBER() Over(partition by UKEY Order by n) rn  
FROM [dbo].Stagetable  
Cross apply (Select n from nums) d(n)   
Where n <= len(INSCODE)   
AND substring('^' + INSCODE, n, 1) = '^'  
)  
  
,cteUKEY as (  
SELECT n, UKEY ,  
Substring(UKEY , n, charindex('^', UKEY  + '^', n) - n)  vals  
,ROW_NUMBER() Over(partition by UKEY Order by n) rn  
FROM [dbo].Stagetable  
Cross apply (Select n from nums) d(n)   
Where n <= len(UKEY)   
AND substring('^' + UKEY, n, 1) = '^'  
)  
  
select m.vals AUTONUM   
,m2.vals UKEY  
,m3.vals INSCODE  
,m4.vals ACTION_TIMESTAMP  
from cteAUTONUM m   
left join cteUKEY m2 on m.UKEY=m2.UKEY and m.rn=m2.rn  
 left join cteINSCODE m3 on m.UKEY=m3.UKEY and m.rn=m3.rn  
 left join cteACTION_TIMESTAMP m4 on m.UKEY=m4.UKEY and m.rn=m4.rn  
 order by m2.vals,m3.vals,m4.vals,m.rn