DataTables sorts strings instead of numeric

Question

I am using jquery.datatables to show numbers in datatables columns. Numbers are formatted to have spaces in between thousands unit (like 123 456 789). Unfortunately, this number formatting provoques a string sorting instead of a number sorting (see the screenshot at the end of this question).

I have identified that:

function _fnSort(oSettings, bApplyClasses) { is the core function for sorting.
In this function, the dynamic function sorting approach is used (the one executed if if (!window.runtime) { is true)

The string sorting functions used are the two following functions.

/*
* text sorting
*/
"string-asc": function(a, b) {
    var x = a.toLowerCase();
    var y = b.toLowerCase();
    return ((x < y) ? -1 : ((x > y) ? 1 : 0));
},

"string-desc": function(a, b) {
    var x = a.toLowerCase();
    var y = b.toLowerCase();
    return ((x < y) ? 1 : ((x > y) ? -1 : 0));
},

My knowledge in javascript is pretty poor, what would be the best approach here?

Tweak the string sorting function to detect the number thousands formatted case, and do the comparison (I guess this would be pretty slow on large data set).
Provide a numerical sorting function dedicated for number thousands formatted? In that case
- how would you code that?
- how could I indicate to the core sorting function, to use this special numerical sorting function?

Here is what the sorting look like by now:

enter image description here

Denys Séguret · Accepted Answer · 2012-07-05 10:20:36Z

10

To sort this kind of values, you can use this sort function :

var sortFunction=function(a, b){
    var ia = parseInt(a.split(' ').join(''), 10);
    var ib = parseInt(b.split(' ').join(''), 10);
    return ia-ib;
};

Test :

var data = ['3 333', '100 333', '22 000', '1 333'];
console.log(data.sort(sortFunction));

With a reasonable number of values, this will be fast enough. You shouldn't try to enrich the data if you don't detect performance problems.

EDIT :

In fact, the documentation proposes an appropriate (similar) sorting function :

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
    "formatted_numbers-pre": function ( a ) {
        a = (a==="-") ? 0 : a.replace( /[^\d\-\.]/g, "" );
        return parseFloat( a );
    },

    "formatted_numbers-asc": function ( a, b ) {
        return a - b;
    },

    "formatted_numbers-desc": function ( a, b ) {
        return b - a;
    }
} );

After having added this extension, you just have to set the sType of your column.

edited Jul 5, 2012 at 10:20

answered Jul 5, 2012 at 9:28

Denys Séguret

384k90 gold badges813 silver badges780 bronze badges

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

5 Comments

Patrick from NDepend team Over a year ago

Awesome! But then how could I force _fnSort() to use this sorting function for the concerned column?

Allan Jardine Over a year ago

Author of DataTables here - nice answer - up voted. I'd suggest marking as correct/accepted :-). Only typo is the sType as an uppercase 'T': datatables.net/ref#sType . Also worth pointing out that there are lots of other sorting plug-ins (some with type detection counterparts) - datatables.net/plug-ins/sorting

Denys Séguret Over a year ago

Hi. Sorry for the typo, now fixed.

Patrick from NDepend team Over a year ago

Great! I'll try this this afternoon and will let you know :)

Patrick from NDepend team Over a year ago

Ok, I stumble on another issue: how to "set the sType of my column". I can see the javascript code in the section of the doc "How to use DataTables plug-in sorting functions functions (type based)". How can I use this javascript code if I have plenty of dataTable defined in my html? Ideally I'd imagine something like <thead><th sType="numeric2">XX</th></thead>

Patrick from NDepend team · Accepted Answer · 2012-07-06 09:37:01Z

Ok, after a lot of search, I found an alternative solutions. The solution proposed by dystroy and Allan Jardine is certainly cleaner. But it implies touching the HTML and in my case, touching the HTML provoked tricky message box like this one below.

enter image description here

So my solution is to just touch the javascript string sorting algo, to switch between the numeric and textual cases. I wish it could be cleaner by using something like isDigit(sa.charAt[0]) but it just don't work despite all my attempts. At least this solution works and doesn't imply any noticeable performance cost:

    /*
    * text + integer sorting
    */
    "string-asc": function(a, b) {
       var sa = a.toString();
       if(sa.length > 0) {
          // Don't know why, isDigit(sa.charAt[0]) doesn't work??
          var ca = sa.substring(0,1);
          if(ca === "0" || ca === "1" || ca === "2" || ca === "3" || ca === "4" || ca === "5" || ca === "6" || ca === "7" || ca === "8" || ca === "9") {
             var x1 = parseInt(a.split(' ').join(''), 10);
             var y1 = parseInt(b.split(' ').join(''), 10);
             return x1 - y1;
          }
       }
       var x = a.toLowerCase();
       var y = b.toLowerCase();
       return ((x < y) ? -1 : ((x > y) ? 1 : 0));
    },

    "string-desc": function(a, b) {
        var sa = a.toString();
        if(sa.length > 0) {
           var ca = sa.substring(0,1);
           if(ca === "0" || ca === "1" || ca === "2" || ca === "3" || ca === "4" || ca === "5" || ca === "6" || ca === "7" || ca === "8" || ca === "9") {
              var x1 = parseInt(a.split(' ').join(''), 10);
              var y1 = parseInt(b.split(' ').join(''), 10);
              return y1 - x1;
           }
        }
        var x = a.toLowerCase();
        var y = b.toLowerCase();
        return ((x < y) ? 1 : ((x > y) ? -1 : 0));
    },

Steven · Accepted Answer · 2015-12-16 20:03:05Z

0

To anyone that is reading this and want the complete answer for space between numbers :

  jQuery.extend( jQuery.fn.dataTableExt.oSort, {
      "formatted_numbers-pre": function ( a ) {
        a = (a===" ") ? 0 : a.replace( /[^\d\-\.]/g, "" );
        return parseFloat( a );
      },

      "formatted_numbers-asc": function ( a, b ) {
        return a - b;
      },

      "formatted_numbers-desc": function ( a, b ) {
        return b - a;
      }
  } );

    $('.myTable').DataTable({
      "columnDefs": [
        { "type": "formatted_numbers", "targets": 4 }
      ],
    });
  }

answered Dec 16, 2015 at 20:03

Steven

8321 gold badge8 silver badges28 bronze badges

Comments

General Failure · Accepted Answer · 2016-12-08 11:09:28Z

Simply set decimal point when building the DataTable like this:

var main_table = $('#main_list').DataTable({
    ajax: {
        url: "/api/your/data",
        dataSrc: ''
    },
    columns: [
        { data: "Col1" },
        { data: "Col2" },
        { data: "Col3" },
        { data: "Col4" }
    ],
    language: {
        /* -----> */ "decimal": ",", // <---------
        "emptyTable": "Keine Daten in der Tabelle verfügbar",
        "info": "Anzeigen von _START_ bis _END_ von _TOTAL_ Einträgen",
        "infoEmpty": "Anzeigen von 0 bis 0 von 0 Einträgen",
        "infoFiltered": "(filtriert von_MAX_ Gesamteinträge)",
        "infoPostFix": "",
        /* -----> */ "thousands": ".", // <---------
        "lengthMenu": "_MENU_ Einträge anzeigen",
        "loadingRecords": "Laden...",
        "processing": "Verarbeitung...",
        "search": "Suche:",
        "zeroRecords": "Keine passenden Datensätze gefunden",
        "paginate": {
            "first": "Erste",
            "last": "Letzte",
            "next": "Nächste",
            "previous": "Vorherige"
        },
        "aria": {
            "sortAscending": ": aufsteigend sortieren",
            "sortDescending": ": absteigend sortieren"
        }
    },
    columnDefs: [
        {//set german formatting

            render: function (data, type, row) {
                return formatDE(data,2);
            },
            targets: [2, 4, 5]
        },
        {

            render: function (data, type, row) {
                return formatDE(data,0);
            },
            targets: [3]
        }
    ],
    pageLength: 50});

If you dig in more into jquery.dataTables.js you will find that they have a function which determines the type of every column value and catches the format

Collectives™ on Stack Overflow

DataTables sorts strings instead of numeric

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related