datatables... Sort don't work with numeric-comma... Why?

Question

I use the jquery datatables with server_processing. This reminds me server_processing in datatables numbers (or words?) with the ',' separator for decimal.

$ inter = trim ($ aRow ['carat']);
$ row [] = number_format ($ inter, 2, ',', '');

When I click on the arrows ascending and descending, sorting does not occur in the numbers method, but the method texts. ex. 10.01> 9.99 (in numbers)

How make it work with my numbers?

I had a headache with this too and ended up using unformatted numbers for the data and then mRender to format it myself. — Karl Forshaw, Mar 13 '13 at 13:47
you mentioned that you used server_processing, do you use the server side sort or just get data and sort it at client side? — OQJF, Mar 17 '13 at 03:09
post more code , your english is too broken to understand what your problem is without more code — Jay Rizzi, May 31 '13 at 15:11

score 2 · Answer 1 · edited May 23 '17 at 12:33

In JavaScript only dots are valid as decimal separator. As DataTables uses JavaScript to sort columns values, it will consider number with comma separator as strings. However, this problem can be easily overtaken with a sort plug-in.

jQuery.extend(jQuery.fn.dataTableExt.oSort, {
    "numeric-comma-pre": function (a) {
        // prepare number
        a = +(a.replace(",", "."));
        a = (isNaN(a)) ? Number.MAX_VALUE : a;
        return a;
    },
    "numeric-comma-asc": function (a, b) {
        return ((a < b) ? -1 : ((a > b) ? 1 : 0));
    },
    "numeric-comma-desc": function (a, b) {
        return ((a < b) ? 1 : ((a > b) ? -1 : 0));
    }
});

The previous code block defines a new data type, to be used by DataTables to sort columns. This code is an adaptation of an example that can be found in DataTables official site.

The first function in the plug-in object, is the one that converts the cell value to number, so that it can be sorted by the following functions. In this case I followed a suggestion from this post, and used the unary operator to convert the value to number. If the value is not a number then NaN is returned, and I change it to Number.MAX_VALUE. I do it this way because I choose to move invalid numbers last when sorting ascending.

After that, only remains create the datatable, and define the new data type for the columns we want.

jQuery("#myTable").dataTable( {
    "aoColumnDefs": [
        { "sType": "numeric-comma", "aTargets": [2,3] }
    ]
});

Supposing that third and fourth columns have comma separated numbers, they now must be sorted correctly.

Maybe you missed the plugin part. I reformulated my answer to include an example of how to do this. — Sergio Pinto, Mar 17 '13 at 01:40
@Jean-YvesDeman you need to calm down and post some more code if you need help — Jay Rizzi, May 31 '13 at 15:09

score 2 · Answer 2 · edited Nov 28 '14 at 12:20

I tried the example of datatables page. It works fine to me.

The data should be decimal format

https://datatables.net/examples/plug-ins/sorting_sType.html

$(document).ready(function() {

    jQuery.fn.dataTableExt.oSort['numeric-comma-asc'] = function(a, b) {
        var x = (a == "-") ? 0 : a.replace(/,/, ".");
        var y = (b == "-") ? 0 : b.replace(/,/, ".");
        x = parseFloat(x);
        y = parseFloat(y);
        return ((x < y) ? -1 : ((x > y) ? 1 : 0));
    };

    jQuery.fn.dataTableExt.oSort['numeric-comma-desc'] = function(a, b) {
        var x = (a == "-") ? 0 : a.replace(/,/, ".");
        var y = (b == "-") ? 0 : b.replace(/,/, ".");
        x = parseFloat(x);
        y = parseFloat(y);
        return ((x < y) ? 1 : ((x > y) ? -1 : 0));
    };

    oTable = $('#grid').dataTable({
        "bJQueryUI": true,
        "sPaginationType": "full_numbers",
        "aoColumns": [
            null,
            null,
            null, {
                "sType": "numeric-comma"
            },
            null,
            null
        ]
    });
});

score 2 · Answer 3 · answered Dec 20 '16 at 13:17

Try inserting the following code on your head. Remeber to change the "example" for your datatable name.

$(document).ready(function() {
    $('#example').DataTable( {
        "language": {
            "decimal": ",",
            "thousands": "."
        }
    } );
} );

score 0 · Answer 4 · answered May 31 '13 at 15:11

if you are doing server-side processing implementation of Datatables, you cannot use any sort plugins or sType variables, because they all reside client side

You would need to update your database query to properly sort a column as a number/string, then use mRender or javascript to update the column format, so that the sort really has noting todo with the column format

example : returning money as a number from the database, then using mRender to add commas/currency types

Vinicius José Latorre · Answer 5 · 2014-12-13T20:55:27.303

0

The latest dataTables version (1.10.4) handles formatted numeric sorting without any extension, that is, puts $1,000.00 before $900.00 when sorting in descending order and puts $900.00 before $1,000.00 when sorting in ascending order.

See num-fmt in http://datatables.net/reference/option/columns.type.

edited Dec 13 '14 at 20:55

answered Dec 13 '14 at 20:46

Vinicius José Latorre

2,661
1
13
6

datatables... Sort don't work with numeric-comma... Why?

5 Answers5