WITH SampleData AS ( SELECT val=REPLACE(val, '.', '#') FROM (VALUES ('10.0||14.5'), ('2||34'), ('7.1||19'), ('4||11.7') ) v(val))SELECT C1=REPLACE(PARSENAME(REPLACE(val, '||', '.'), 2), '#', '.') ,C2=REPLACE(PARSENAME(REPLACE(val, '||', '.'), 1), '#', '.')FROM SampleData;
--just to keep the examples simple --I'm using a temp table for the sample dataIF OBJECT_ID('tempdb..#TempTable') IS NOT NULLDROP TABLE #TempTableCREATE TABLE #TempTable ( [Val] NVARCHAR(50) NULL)INSERT INTO #TempTableSELECT '10.0||14.5' UNION ALLSELECT '2||34' UNION ALLSELECT '7.1||19' UNION ALLSELECT '4||11.7'
SELECT dsk.ItemFROM #TempTable AS sdCROSS APPLY dbo.DelimitedSplit8K(sd.val,'|') AS dsk WHERE ItemNumber IN (1,3)/*Item10.014.52347.119411.7*/
SELECT dsk1.Item AS FirstVal ,dsk2.Item AS SecondVal FROM #TempTable AS sd CROSS APPLY dbo.DelimitedSplit8K(sd.val,'|') AS dsk1 CROSS APPLY dbo.DelimitedSplit8K(sd.val,'|') AS dsk2 WHERE dsk1.ItemNumber = 1 AND dsk2.ItemNumber = 3 /*FirstVal SecondVal10.0 14.52 347.1 194 11.7*/