sql server - SQL - how to order fields with values like 1.4.2 -

how write sql order text field called "sequentialorder" values 7.5.5 records come out in order of

    1.2.4     2.3.8     11.3.4 

and not this

    11.3.4              1.2.4     2.3.8 

you have parse given version strings access components implement order by semantics. there many ways it. try avoid clr code, here comes suggestion: parse version using dot separator, convert each value number, aggregate columns , use proper order byclause:

-- script uses function [dbo].[delimitedsplit8k] split delimited -- string value multiple rows; published , avalable @ -- http://www.sqlservercentral.com/articles/tally+table/72993/  -- setup create table version (id varchar(20)) insert version (id) values('1.2.4'); insert version (id) values('2.3.8'); insert version (id) values('11.3.4');  -- cte query ; inrows (   -- split version id many rows   select ver.id, dlm.itemnumber, convert(int, dlm.item) item   version ver   cross apply [dbo].[delimitedsplit8k](ver.id, '.') dlm ) , incols (   -- aggregate rows groupped id   select inr.id   , max(case when inr.itemnumber = 1 inr.item else null end) [major]   , max(case when inr.itemnumber = 2 inr.item else null end) [minor]   , max(case when inr.itemnumber = 3 inr.item else null end) [revision]   inrows inr   group inr.id ) select * incols inc order inc.major, inc.minor, inc.revision;  -- results ================================================ -- id                   major       minor       revision -- -------------------- ----------- ----------- ----------- -- 1.2.4                1           2           4 -- 2.3.8                2           3           8 -- 11.3.4               11          3           4