There is a known issue with the XML method of string splitting where it can perform horribly because of repeated re-evaluations of the REPLACE
and the CAST
to XML
. More details in this blog post.
The best performing method is CLR but it sounds as though this will not be an option for you. As the maximum split index of interest is known in advance a more robust method, avoiding XML, might be
SELECT Execution_ID,
Intake_Generic_ID,
File_Name,
F1,
LTRIM(RTRIM(SUBSTRING(F, C0, C1- C0 - 1))) AS TransactionDate,
LTRIM(RTRIM(SUBSTRING(F, C1, C2- C1 - 1))) AS TransactionNum,
LTRIM(RTRIM(SUBSTRING(F, C2, C3- C2 - 1))) AS Interchange,
LTRIM(RTRIM(SUBSTRING(F, C3, C4- C3 - 1))) AS Gantry,
-- LTRIM(RTRIM(SUBSTRING(F, C4, C5- C4 - 1))) AS Unknown,
LTRIM(RTRIM(SUBSTRING(F, C5, C6- C5 - 1))) AS GantryDirection,
LTRIM(RTRIM(SUBSTRING(F, C6, C7- C6 - 1))) AS GantryEntryExit,
LTRIM(RTRIM(SUBSTRING(F, C7, C8- C7 - 1))) AS RTCID,
LTRIM(RTRIM(SUBSTRING(F, C8, C9- C8 - 1))) AS Vehicle_Classification_Cd,
LTRIM(RTRIM(SUBSTRING(F, C9, C10- C9 - 1))) AS VehicleClassification,
LTRIM(RTRIM(SUBSTRING(F,C10, C11-C10 - 1))) AS Txn_Iden_Cd,
LTRIM(RTRIM(SUBSTRING(F,C11, C12-C11 - 1))) AS TransactionIdentification
FROM [dbo].[Intake_Generic]
CROSS APPLY (VALUES(1, F1 + REPLICATE('~',12))) V0(C0,F)
CROSS APPLY (VALUES(1 + CHARINDEX('~',F, C0 ))) V1 (C1)
CROSS APPLY (VALUES(1 + CHARINDEX('~',F, C1 ))) V2 (C2)
CROSS APPLY (VALUES(1 + CHARINDEX('~',F, C2 ))) V3 (C3)
CROSS APPLY (VALUES(1 + CHARINDEX('~',F, C3 ))) V4 (C4)
CROSS APPLY (VALUES(1 + CHARINDEX('~',F, C4 ))) V5 (C5)
CROSS APPLY (VALUES(1 + CHARINDEX('~',F, C5 ))) V6 (C6)
CROSS APPLY (VALUES(1 + CHARINDEX('~',F, C6 ))) V7 (C7)
CROSS APPLY (VALUES(1 + CHARINDEX('~',F, C7 ))) V8 (C8)
CROSS APPLY (VALUES(1 + CHARINDEX('~',F, C8 ))) V9 (C9)
CROSS APPLY (VALUES(1 + CHARINDEX('~',F, C9) )) V10(C10)
CROSS APPLY (VALUES(1 + CHARINDEX('~',F, C10))) V11(C11)
CROSS APPLY (VALUES(1 + CHARINDEX('~',F, C11))) V12(C12)
Or another way would be to use Jeff Moden's approach from this article with the necessary adjustments to work without creating a function and to PIVOT
the results into a single row.
WITH E1(N)
AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1), --10E+1 or 10 rows
E2(N)
AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N)
AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max
SELECT Execution_ID,
Intake_Generic_ID,
File_Name,
F1,
[1] AS TransactionDate,
[2] AS TransactionNum,
[3] AS Interchange,
[4] AS Gantry,
-- [5] AS Unknown,
[6] AS GantryDirection,
[7] AS GantryEntryExit,
[8] AS RTCID,
[9] AS Vehicle_Classification_Cd,
[10] AS VehicleClassification,
[11] AS Txn_Iden_Cd,
[12] AS TransactionIdentification
FROM dbo.Intake_Generic ig
CROSS APPLY (SELECT *
FROM (SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = LTRIM(RTRIM(SUBSTRING(ig.F1, l.N1, l.L1)))
FROM (SELECT N1 = s.N1,
L1 = ISNULL(NULLIF(CHARINDEX('~', ig.F1, s.N1), 0) - s.N1, 8000)
FROM (SELECT N1 = 1
UNION ALL
SELECT N1 = Nums.N + 1
FROM (SELECT TOP (ISNULL(DATALENGTH(ig.F1), 0))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E4) Nums(N)
WHERE SUBSTRING(ig.F1, Nums.N, 1) = '~') s) l)
f
PIVOT (MAX(Item)
FOR ItemNumber IN ([1], [2], [3],
[4], [5], [6],
[7], [8], [9],
[10], [11], [12])) P
) CA;
Intake_Generic.F1
stored in that horrible format to begin with? And why do you need to use XML at all? \$\endgroup\$