Or another (just for fun 😉 )

WITH SampleData (ID, MyString) AS

(

SELECT 1, '1:1,3,5,7,4:56,43,58,5:34,67r,234'

),

split AS

(

SELECT b.ItemNumber,b.Item

FROM SampleData a

CROSS APPLY dbo.DelimitedSplit8K(MyString, ',') b

)

SELECTLEFT(s2.Item,CHARINDEX(':',s2.Item)-1),

CASE CHARINDEX(':', s1.Item) WHEN 0 THEN s1.Item ELSE SUBSTRING(s1.Item,CHARINDEX(':', s1.Item)+1,255) END

FROMsplit s1

CROSS APPLY (SELECT TOP 1 * FROM split WHERE ItemNumber < s1.ItemNumber AND Item LIKE '%:%' ORDER BY ItemNumber DESC) s2

Really horrible looking execution plan though 😀