quote:

---

sorry, yea there are more that 4 digits, infinate number...

---

SELECT MyCol
  FROM MyTable M
  CROSS JOIN (
SELECT MAX(LEN(MyCol)) As MaxLen
  FROM MyTable ) L
ORDER BY LEFT(MyCol + REPLICATE('0', MaxLen), MaxLen)

DECLARE @sort TABLE(point VARCHAR(55))

INSERT @sort(point)
	SELECT '1' UNION ALL
	SELECT '1.2' UNION ALL
	SELECT '1.3' UNION ALL
	SELECT '1.4' UNION ALL
	SELECT '1.5' UNION ALL
	SELECT '1.6' UNION ALL
	SELECT '1.7' UNION ALL
	SELECT '1.4.1' UNION ALL
	SELECT '1.4.2' UNION ALL
	SELECT '1.6.1' UNION ALL
	SELECT '1.6.2' UNION ALL
	SELECT '1.6.1.1' UNION ALL
	SELECT '1.6.1.2'

SELECT point FROM @sort

SELECT CHARINDEX('.',point,CHARINDEX('.',point)+1) FROM @sort
SELECT LEFT(point,CHARINDEX('.',point)) FROM @sort
SELECT point
FROM @sort
ORDER BY
	CASE 
		WHEN CHARINDEX('.',point,CHARINDEX('.',point)+1) = 0 THEN CAST(point AS DECIMAL(9,5))
		ELSE CAST(LEFT(point,CHARINDEX('.',point)) + REPLACE(RIGHT(point,LEN(point)-CHARINDEX('.',point)),'.','') AS DECIMAL(9,5)) END

quote:

---

done, done and done...

---

table
A special data type that can be used to store a result set for later processing. Its primary use is for temporary storage of a set of rows, which are to be returned as the result set of a table-valued function.

Syntax


Note  Use DECLARE @local_variable to declare variables of type table.


table_type_definition ::= 
    TABLE ( { column_definition | table_constraint } [ ,...n ] ) 

column_definition ::= 
    column_name scalar_data_type 
    [ COLLATE collation_definition ] 
    [ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ] 
    [ ROWGUIDCOL ] 
    [ column_constraint ] [ ...n ] 

column_constraint ::= 
    { [ NULL | NOT NULL ] 
    | [ PRIMARY KEY | UNIQUE ] 
    | CHECK ( logical_expression ) 
    } 

table_constraint ::= 
    { { PRIMARY KEY | UNIQUE } ( column_name [ ,...n ] ) 
    | CHECK ( search_condition ) 
    } 

Arguments
table_type_definition

Is the same subset of information used to define a table in CREATE TABLE. The table declaration includes column definitions, names, data types, and constraints. The only constraint types allowed are PRIMARY KEY, UNIQUE KEY, and NULL. A user-defined data type cannot be used as a column scalar data type.

For more information about the syntax, see CREATE TABLE, CREATE FUNCTION, and DECLARE @local_variable.

collation_definition

Is the collation of the column consisting of a Microsoft® Windows™ locale and a comparison style, a Windows locale and the binary notation, or a Microsoft SQL Server™ collation.

Remarks
Functions and variables can be declared to be of type table. table variables can be used in functions, stored procedures, and batches.

Use table variables instead of temporary tables, whenever possible. table variables provide the following benefits: 

A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared. 
Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

INSERT INTO table_variable EXEC stored_procedure 

SELECT select_list INTO table_variable statements.

table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.

CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined functions.


table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.


Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources. 
Assignment operation between table variables is not supported. In addition, because table variables have limited scope and are not part of the persistent database, they are not impacted by transaction rollbacks.

quote:

---

Originally posted by AndrewMurphy

get your data to look like.....

001.000.000.000
001.002.000.000
001.003.000.000
001.004.000.000
001.004.001.000
001.004.002.000
001.005.000.000
001.006.000.000
001.006.001.000
001.006.001.001
001.006.001.002
001.006.002.000
001.007.000.000


and you will have no problem sorting...

you need to add/pad all records to make sure the structures have same number of characters.

---

declare @myTable table (unPadded varchar(1000), padded varchar(1000))
Insert Into @myTable
Select '1', null
Union All Select '1.2', null
Union All Select '1.3', null
Union All Select '1.6.1.2', null
Union All Select '10.1.1', null
Union All Select '1.5', null
Union All Select '1.6.1.1', null
Union All Select '1.6', null
Union All Select '1.6.2', null
Union All Select '1.7', null
Union All Select '10.1', null
Union All Select '1.4.1', null
Union All Select '10.1.2', null
Union All Select '1.4', null
Union All Select '1.4.2', null
Union All Select '1.6.1', null
Union All Select '100.1', null


Select * From @myTable

Declare @pad varchar(10)
Set @pad = '000'

While exists(Select 1 From @myTable Where charindex('.',unPadded)>0)
Begin
	Update @myTable
	Set
		unPadded = right(unPadded,len(unPadded)-charindex('.',unPadded)),
		padded = isnull(padded+'.','') + right(@pad + left(unPadded,charindex('.',unPadded)-1),3)
	From @myTable 
	Where charindex('.',unPadded)>0
End

Update @myTable
Set
	unPadded = null,
	padded = isnull(padded+'.','') + right(@pad + isnull(unPadded,''),3)
From @myTable 

Select * From @myTable Order By padded