Sort Order on column with multiple decimal places

I'm using SQL Server 2008 R2 and Crystal Reports XI

I have a field that is of type nvarchar

it contains the contents of a book so the data is like

1 
3.3
1.1
4.5.6
1.4.3.1.1
11.2
....

How would I sort on this column such that it shows up on the report as

1
1.1
1.4.3.1.1
3.3
4.5.6
11.2
...

---

If you know the max number of decimals then you could break apart the string into parts and sort on those individually. For example, if you know you'll only have a max of 4 decimals (separating 5 different numbers) then you could create 5 formulas each representing a piece of the string.

//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 your report first by {@Num1} and then by {@Num2} etc.

---

It is possible to sort them in T-SQL by converting the 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 see this SQL Fiddle for a demonstration.

链接地址: http://www.djcxy.com/p/72280.html

上一篇: java扫描仪只读取前2048个字节

下一篇: 在具有多个小数位的列上排序