Lowell (3/12/2013)
...the default DelimitedSplit8K is limited to a single char delimiter, which is one tripwire, since he's got dbl pipes for the delimiter. so he'd have to adapt it, or find-and-replace the dbl piple with a single char, and either solution might not be obvious.
That statement is generally true, but in this case he still has just a single pipe character that can be used to split the string with DelimitedSplit8K. So no replaces necessary at all...
[EDIT: I see Eugene posted basically the same thing above. Great minds think alike! 🙂 ]
--just to keep the examples simple
--I'm using a temp table for the sample data
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[Val] NVARCHAR(50) NULL)
INSERT INTO #TempTable
SELECT '10.0||14.5' UNION ALL
SELECT '2||34' UNION ALL
SELECT '7.1||19' UNION ALL
SELECT '4||11.7'
If you just need all the values in a single column how simple can it get?
DelimitedSplit8K is far from overkill. Why use a hammer when you have a nail gun?
SELECT
dsk.Item
FROM
#TempTable AS sd
CROSS APPLY
dbo.DelimitedSplit8K(sd.val,'|') AS dsk
WHERE
ItemNumber IN (1,3)
/*
Item
10.0
14.5
2
34
7.1
19
4
11.7
*/
And if you need to keep the pairs in their own columns just add another CROSS APPLY.
Still no replaces or string manipulation is necessary...DelimitedSplit8K will do its magic!
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
/*
FirstValSecondVal
10.014.5
234
7.119
411.7
*/