Solution 2 by Raja is very good. Another way is to split numbers into columns using
CTE[
^] and
PIVOT[
^]:
DECLARE @tmp TABLE (MyNumber VARCHAR(30))
INSERT INTO @tmp (MyNumber)
SELECT '1.0.0.0.0'
UNION ALL SELECT '10.0.0.0.0'
UNION ALL SELECT '11.0.0.0.0'
UNION ALL SELECT '12.0.0.0.0'
UNION ALL SELECT '2.0.0.0.0'
UNION ALL SELECT '3.0.0.0.0'
UNION ALL SELECT '4.0.0.0.0'
UNION ALL SELECT '5.0.0.0.0'
UNION ALL SELECT '6.0.0.0.0'
UNION ALL SELECT '7.0.0.0.0'
UNION ALL SELECT '8.0.0.0.0'
UNION ALL SELECT '9.0.0.0.0'
;WITH Parts AS
(
SELECT 1 AS PartNo, CONVERT(INT,LEFT(MyNumber, CHARINDEX('.',MyNumber)-1)) AS Number, RIGHT(MyNumber, LEN(MyNumber) - CHARINDEX('.',MyNumber)) AS Remainder
FROM @tmp
WHERE CHARINDEX('.',MyNumber)>0
UNION ALL
SELECT PartNo+1 AS PartNo, CONVERT(INT,LEFT(Remainder, CHARINDEX('.',Remainder)-1)) AS Number, RIGHT(Remainder, LEN(Remainder) - CHARINDEX('.',Remainder)) AS Remainder
FROM Parts
WHERE CHARINDEX('.',Remainder)>0
UNION ALL
SELECT PartNo+1 AS PartNo, CONVERT(INT,Remainder) AS Number, NULL AS Remainder
FROM Parts
WHERE CHARINDEX('.',Remainder)=0
)
SELECT [RowNo], [1], [2], [3], [4], [5]
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY [PartNo] ORDER BY [PartNo]) AS RowNo, PartNo, Number
FROM Parts
) AS DT
PIVOT(MAX([Number]) FOR [PartNo] IN ([1], [2], [3], [4], [5])) AS UNPVT
ORDER BY [1], [2], [3], [4], [5]
Output:
[RowNo] [1] [2] [2] [4] [5]
1 1 0 0 0 0
5 2 0 0 0 0
6 3 0 0 0 0
7 4 0 0 0 0
8 5 0 0 0 0
9 6 0 0 0 0
10 7 0 0 0 0
11 8 0 0 0 0
12 9 0 0 0 0
2 10 0 0 0 0
3 11 0 0 0 0
4 12 0 0 0 0