Now I'm fairly sure someone is going to think that I'm crazy and perhaps I am, but why not use XML for this?

Let's set up the incoming data just slightly differently:

DECLARE @t TABLE (ItemID INT, strcol VARCHAR(100))

INSERT INTO @t

SELECT 222, 'H;123442;23%;78.99;52000990889'

UNION ALL SELECT 222, 'L;socks;blue;100'

UNION ALL SELECT 222, 'L;socks;red;200'

UNION ALL SELECT 223, 'H;123443;23%;500.99;52000990111'

UNION ALL SELECT 223, 'L;skirt;blue;100'

UNION ALL SELECT 223, 'L;pants;red;200'

Then reformat the delimited strings into something that can be CAST to XML datatype, and put this into a temporary table (or table variable):

DECLARE @x TABLE (ItemID INT, xmlcol XML)

;WITH CreateXML AS (

SELECT ItemID, strcol

,xmlcol=CASE SUBSTRING(strcol, 1, 1)

WHEN 'H' THEN '<h>' + STUFF(deliveryPlace + '</deliveryPlace></h>', 1, 1, '')

ELSE '<d>' + STUFF(qty + '</qty></d>', 1, 1, '') END

,rn=ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY (SELECT NULL))

FROM @t

CROSS APPLY (

SELECT STUFF(strcol, CHARINDEX(';', strcol), 1, '<inv_number>')) a(inv_number)

CROSS APPLY (

SELECT STUFF(inv_number, CHARINDEX(';', inv_number), 1, '</inv_number><vat>')) b(vat)

CROSS APPLY (

SELECT STUFF(vat, CHARINDEX(';', vat), 1, '</vat><amount>')) c(amount)

CROSS APPLY (

SELECT STUFF(amount, CHARINDEX(';', amount), 1, '</amount><deliveryPlace>')) d(deliveryPlace)

CROSS APPLY (

SELECT STUFF(strcol, CHARINDEX(';', strcol), 1, '<itemDescription>')) e(itemDescription)

CROSS APPLY (

SELECT STUFF(itemDescription, CHARINDEX(';', itemDescription), 1, '</itemDescription><color>')) f(color)

CROSS APPLY (

SELECT STUFF(color, CHARINDEX(';', color), 1, '</color><qty>')) g(qty)

),

CreateXML2 AS (

SELECT ItemID

,xmlcol=CAST((

SELECT CASE WHEN SUBSTRING(xmlcol, 2, 1) = 'h' THEN '<ItemID>' + CAST(ItemID AS VARCHAR(5)) + '</ItemID> ' ELSE '' END +

xmlcol

FROM CreateXML b

WHERE a.ItemID = b.ItemID

ORDER BY rn

FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') AS XML)

FROM CreateXML a

GROUP BY ItemID

)

INSERT INTO @x

SELECT ItemID, xmlcol

FROM CreateXML2

I know, I know. It looks pretty ugly. But now getting at the data is relatively simple:

-- Extract headers

SELECT x.row.value('(.)[1]', 'INT') AS ItemID

,x.row.value('(/h/inv_number)[1]', 'VARCHAR(50)') AS inv_number

,x.row.value('(/h/vat)[1]', 'VARCHAR(50)') AS vat

,x.row.value('(/h/amount)[1]', 'MONEY') AS amount

,x.row.value('(/h/deliveryPlace)[1]', 'VARCHAR(50)') AS deliveryPlace

FROM @x

CROSS APPLY xmlcol.nodes('/ItemID') AS x(row)

-- Extract details

SELECT y.row.value('(.)[1]', 'VARCHAR(50)') AS ItemID

,x.row.value('(/h/inv_number)[1]', 'VARCHAR(50)') AS inv_number

,x.row.value('(./itemDescription)[1]', 'VARCHAR(50)') AS itemDescription

,x.row.value('(./color)[1]', 'VARCHAR(50)') AS color

,x.row.value('(./qty)[1]', 'INT') AS qty

FROM @x

CROSS APPLY xmlcol.nodes('/d') AS x(row)

CROSS APPLY xmlcol.nodes('/ItemID') AS y(row)

:hehe: Jeez, Louise! What was I thinking! :w00t:

I must have too much spare time on my hands.