Column order with multiple decimal places

Question

I am using SQL Server 2008 R2 and Crystal Reports XI

I have a field like nvarchar

it contains the contents of the book, so the data is similar to

1 3.3 1.1 4.5.6 1.4.3.1.1 11.2 .... 

How should I sort by this column so that it appears in the report as

 1 1.1 1.4.3.1.1 3.3 4.5.6 11.2 ... 

Answer 1

If you know the maximum number of decimal places, you can split the string into parts and sort them individually. For example, if you know that you will have a maximum of 4 decimal places (division of 5 different numbers), you can create 5 formulas that represent part of a line.

 //Formula {@Num1} to isolate most significant number local stringvar array splitString := split({table.string},'.'); if isnumeric(splitString[1]) then tonumber(splitString[1]) else 0 //...and the formula {@Num2} for second most significant number local stringvar array splitString := split({table.string},'.'); if ubound(splitString)>1 and isnumeric(splitString[2]) then tonumber(splitString[2]) else 0 

Now sort the report first {@Num1} , then {@Num2} , etc.

Answer 2

You can sort them in T-SQL by converting values ​​to hierarchyid

 SELECT * FROM ( VALUES ('1'), ('3.3'), ('1.1'), ('4.5.6'), ('1.4.3.1.1'), ('11.2') ) v (version) ORDER BY CAST('/' + version + '/' AS hierarchyid) ; 

Please view SQL Fiddle for a demo.