jQuery DataTables - sort numbers only

Question

Using 1.10.1 and the new data-sort html5 attributes I want to ignore certain cells from the sort order.

My column is mixed with number and text values. Example:

<tr><td data-sort="100.50">100.50 USD</td></tr>
<tr><td data-sort="">Text</td></tr>
<tr><td data-sort="50.00">50.00 USD</td></tr>

When sorting on this column I want the text cells to be ignored. So descending order would be 100,50,Text. Ascending order would be 50,100,Text.

Can I accomplish this with the data-sort attributes only or is there another way?

doesnt' it do exactly that by data-sort? See this -> jsfiddle.net/A3mcM or do I misunderstand? — davidkonrad
– davidkonrad, Commented Jul 15, 2014 at 17:59
@davidkonrad Hey, your example has the text data being at the top in sorting. I only want to sort by numbers, the text cells being at the bottom always — Mark Steggles
– Mark Steggles, Commented Jul 15, 2014 at 19:22
Yes, I did misunderstood :) I figured out a way to accomplish what you want, but not sure if it is sufficient. +1 for a great question! — davidkonrad
– davidkonrad, Commented Jul 15, 2014 at 21:13

davidkonrad · Accepted Answer · 2014-07-17 13:41:55Z

3

I am afraid this cannot be done with data-sort or data-order alone. DataTables will try to sort ascending / descending no matter what, and what you really need is actually two different sorting values for the plain text fields, making them either the highest or the lowest value.

However, thought you maybe could use a custom sorting plug-in for this instead? See the following plugin, that extracts any number from the column, or if a number is not present, setting the sorting value to either Number.NEGATIVE_INFINITY (sorting descending) or Number.POSITIVE_INFINITY (sorting ascending) so plain text columns always are pushed to the bottom :

function sortNumbersIgnoreText(a, b, high) {
    var reg = /[+-]?((\d+(\.\d*)?)|\.\d+)([eE][+-]?[0-9]+)?/;    
    a = a.match(reg);
    a = a !== null ? parseFloat(a[0]) : high;
    b = b.match(reg);
    b = b !== null ? parseFloat(b[0]) : high;
    return ((a < b) ? -1 : ((a > b) ? 1 : 0));    
}
jQuery.extend( jQuery.fn.dataTableExt.oSort, {
    "sort-numbers-ignore-text-asc": function (a, b) {
        return sortNumbersIgnoreText(a, b, Number.POSITIVE_INFINITY);
    },
    "sort-numbers-ignore-text-desc": function (a, b) {
        return sortNumbersIgnoreText(a, b, Number.NEGATIVE_INFINITY) * -1;
    }
});

Updated. The code is cleaned up, and the plugin now sorts any kind of number, that is

Integers, like 123
Decimal numbers, like 123.45
Negative and positive numbers, like -123.00, +123
Scientific numbers, like 12.3e+10
Illegal numbers, like 012345

see demo -> http://jsfiddle.net/6qmkY/

edited Jul 17, 2014 at 13:41

answered Jul 15, 2014 at 21:03

davidkonrad

85.7k17 gold badges211 silver badges273 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

davidkonrad Over a year ago

@Mark Steggles, have made a new answer. Now the plugin sorts integers, illegal numbers and scientific numbers as well, and the code has been cleaned up heavily.

throny Over a year ago

This is REALLY great, thanks! Exactly what I was looking for :)

goxarad784 Over a year ago

Looks like this does not work for dynamically populated datatables

Collectives™ on Stack Overflow

jQuery DataTables - sort numbers only

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related