Sort Order on column with multiple decimal places

Question

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
...

Try implementing a scalar CLR function... You can find one possible implementation **[here](http://stackoverflow.com/questions/34509/natural-human-alpha-numeric-sort-in-microsoft-sql-2005)**. — bastos.sergio, Jun 26 '13 at 16:27

score 3 · Answer 1 · answered Jun 27 '13 at 07:06

3

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.

answered Jun 27 '13 at 07:06

Andriy M

76,112
17
94
154

Brilliant. I forgot about this new for 2008 data type. – Roger Harvest Jun 21 '16 at 11:28

score 1 · Accepted Answer · answered Jun 26 '13 at 16:32

1

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.

answered Jun 26 '13 at 16:32

Ryan

7,212
1
17
30

Thanks for the response - this would have been perfect but the # of decimals is not fixed. – SamB Jun 26 '13 at 17:06
Number of decimals doesn't need to be fixed, you just need to know the maximum number of decimals. If the decimals don't exist for a particular row, use a 0 and it'll still sort correctly. For example, the string "11.2" will be sorted as 11.2.0.0.0 – Ryan Jun 26 '13 at 17:09
yes - that is what I meant (sorry) ... the maximum number of decimals is not a constant. I can sure set that to a high (say like 20 or so) - but is there a way we can get around that ? – SamB Jun 26 '13 at 17:15

Sort Order on column with multiple decimal places

2 Answers2