and another way, via a scalar function:
/*--Results
cellindex containerid containerlength istwincarry operationtype workqueuename
0 QC6_1_DG_3xx 40 false Discharge QC6_1
0 CID_14720xxx 40 false Load QC7_1
0 QC1_1_DG_30x twin20 true Discharge QC1_1
*/
;WITH SSC_CTE (RecordID, OneColumn) AS (
SELECT 1, 'cellindex=0 ,containerid=QC6_1_DG_3xx ,containerlength=40 ,istwincarry=false ,operationtype=Discharge ,workqueuename=QC6_1' UNION ALL
SELECT 2, 'cellindex=0 ,containerid=CID_14720xxx ,containerlength=40 ,istwincarry=false ,operationtype=Load ,workqueuename=QC7_1' UNION ALL
SELECT 3, 'cellindex=0 ,containerid=QC1_1_DG_30x ,containerlength=twin20 ,istwincarry=true ,operationtype=Discharge ,workqueuename=QC1_1')
,IntermediateResults
AS
(
SELECT
dbo.fn_parsename(OneColumn,',',1) As C1,
dbo.fn_parsename(OneColumn,',',2) As C2,
dbo.fn_parsename(OneColumn,',',3) As C3,
dbo.fn_parsename(OneColumn,',',4) As C4,
dbo.fn_parsename(OneColumn,',',5) As C5,
dbo.fn_parsename(OneColumn,',',6) As C6,
SSC_CTE.*
FROM SSC_CTE
)
select
dbo.fn_parsename(C1,'=',2) As cellindex,
dbo.fn_parsename(C2,'=',2) As containerid,
dbo.fn_parsename(C3,'=',2) As containerlength,
dbo.fn_parsename(C4,'=',2) As istwincarry,
dbo.fn_parsename(C5,'=',2) As operationtype,
dbo.fn_parsename(C6,'=',2) As workqueuename,
IntermediateResults.*
FROM IntermediateResults
the function:
CREATE FUNCTION dbo.fn_parsename
(
@pString VARCHAR(7999),
@pDelimiter CHAR(1),
@Occurrance int
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Results VARCHAR(8000)
--===== "Inline" CTE Driven "Tally Table” produces values up to
-- 10,000... enough to cover VARCHAR(8000)
;WITH
E1(N) AS ( --=== Create Ten 1's
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 --10
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4) ,
--===== Do the split
InterResults
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item
FROM cteTally
WHERE N < LEN(@pString) + 2
AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
)
SELECT @Results = Item FROM InterResults WHERE ItemNumber = @Occurrance
return @Results
END --FUNCTION
Lowell