Lowell (4/9/2013)

---

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