Ordering the data by a column which has numbering/indexation type data with multiple decimals-CodePudding

I have a table which has a column called 'clause' with indexation type data such as 1.,1.0, 1.1.1., 1.1.2., 1.10., 1.2., 2., 2.1.1., 3. etc...

In my query, I need to select this data by order by 'clause' column. Column data type can be anything but for now it is nvarchar type. When I run my query -

1.
1.0
1.1.1.
1.1.2.
1.10.
1.2.
2.
2.1.1.
3.

I understand why this is happening but I want to achieve the following result where 1.2 comes before 1.10. Reason being 2 smaller than 10. So I need the following result.

1.
1.0
1.1.1.
1.1.2.
1.2.
1.10.
2.
2.1.1.
3.

Please can you expert advice if this is possible in SQL and how ?

Thanks,

CodePudding user response：

This answer is inspired by

DROP TABLE IF EXISTS #DataTable;

CREATE TABLE #DataTable (
    Clause VARCHAR(20)
);

INSERT INTO #DataTable 
VALUES
('1.'),
('1.0'),
('1.1.1.'),
('1.1.2.'),
('1.10.'),
('1.2.'),
('2.'),
('2.1.1.'),
('3.')

SELECT Clause FROM 
(
    SELECT 
    Clause,
    CAST(REVERSE(PARSENAME(REVERSE(Clause), 1)) AS INT) AS Major,
    CAST(REVERSE(PARSENAME(REVERSE(Clause), 2)) AS INT) AS Minor,
    CAST(REVERSE(PARSENAME(REVERSE(Clause), 3)) AS INT) AS Patch
    FROM #DataTable
) AS T
ORDER BY T.Major, T.Minor, T.Patch