Datatables sort numbers is not working properly

Question

I use datatables on my page, it seems to work fine, except that when you do sorting on the number field, it sorts in a weird way, take a look at the illustration

This one also

I have tried to place

 "aoColumns": [
            null,
            null,
            null,
            null,
            null,
            null,
            { "sType": 'numeric',
            "oCustomInfo":{
    "decimalPlaces":1,
    "decimalSeparator":"."}
            },
            null
        ]

But this seems to further disturbs its behaviour as it becomes unsortable. Do you have a clue ?

Thanks

Scandinavian

read the sorting docs and set type.... can find a lot with google...datatables sort numbers ...including numerous posts on this topic in SO – Aliped

Well, I have tried but non of the solutions seem to work. – Scandinavian

try to remove the aoColumns-definition completely, it should work correct right away without it, if there is not any of the rows that break the impression of a column with decmials!! That is, for instance, if some of the numbers have trailing blanks, and then is treated as strings. – Hydrodynamic

@Hydrodynamic Thanks for your reply, actually I didnt have the column definition at first, but it didnt work, the aoColumns was a work around that didnt work either :) – Scandinavian

@Digitalfortress, I would say you should start trim the values of the column. I am 99.99% certain that you have either some whitespace or similar in at least one of the rows, and then the datatables sorting algorithm goes into "string"-mode. The problem is the data, not DataTables. Have been there too :) – Hydrodynamic

Take a look in this question #24761959 and example mentioned in the answer. – Particle

Answer 1

Your problem is that for some reason the table data are treated as string and NOT as a number. Check the formatting of your values.

If fore example you have something like this:

<table .....>
  <tbody>
    <tr>
      <td><span>1</span></td>
    </td>
    ....
  </tbody>
</table>

Then Datatables will treat the value of that column as a string and not as a Number. This is common if you are outputting the text with a framework such as asp .NET.

See this for a similar problem

Wearable

Answer 2

After hours of debugging and tracking it down I thought I'd share the solution for others.

tldr:

columns:[{data: 'ttl_paid', "render":function(data,type,row,meta){if(type === 'sort' || type === 'type'){if(data != null){ return parseFloat(data);}else{return 0;}}else{if(data != null){ return '£'+data;}else{return '';}}}}]

When you define the columns, you can use the render function. This is used to pre-render the data.

The render function is ran for various purposes and the 2 key ones for this purpose are "type" to determine what data type the column contains and "sort" to sort the column.

So the solution is to specify in the render function:

if(type === 'sort' || type === 'type'){
    //numeric for sorting & determining type
}else{
    //formatted for display
}

That way on load, data tables sees it as a number and when sorting it sorts the number values.

Here is the nicely formatted version:

columns:[
    {
        data: 'ttl_paid', "render": function(data,type,row,meta){
            if(type === 'sort' || type === 'type'){
                if(data != null){
                    return parseFloat(data);
                }else{
                    return 0;
                }
            }else{
                if(data != null){
                    return '£'+data;
                }else{
                    return '';
                }
            }
        }
    }
]

Walters