DataTables sort currency

Question

Please help with live example, how to sort currency in format "34 566.00 ek." in DataTables script.

Here is JSFiddle example: http://jsfiddle.net/HEDvf/643/

$('#example').dataTable({
   "aoColumns": [
    null,
   ],        
  "aaSorting": [[ 0, "desc" ]],
  "bStateSave": false,
  "iDisplayLength": 50,
});

score 12 · Accepted Answer · answered Sep 03 '13 at 16:45

Have a look at the very extensive datatables documentation. There you will find simple solutions to almost all problems you will ever have with datatables. There are for example little plugin functions to add sorting support for currency columns.

An example based on what you got:

// add sorting methods for currency columns
jQuery.extend(jQuery.fn.dataTableExt.oSort, {
    "currency-pre": function (a) {
        a = (a === "-") ? 0 : a.replace(/[^\d\-\.]/g, "");
        return parseFloat(a);
    },
    "currency-asc": function (a, b) {
        return a - b;
    },
    "currency-desc": function (a, b) {
        return b - a;
    }
});

// initialize datatable and explicitly set the column type to "currency"
$('#example').dataTable({
    "aoColumns": [{"sType": "currency"}],
    "aaSorting": [[0, "desc"]],
    "bStateSave": false,
    "iDisplayLength": 50,
});

Links to the documentation:

Sorting: http://datatables.net/plug-ins/sorting#currency

Datatables is also able to automatically detect column types, but it gets a bit complicated with all the different formattings. Type-detection: http://datatables.net/plug-ins/type-detection#currency

Thank you very much. Works well. Here is live example, after changes. http://jsfiddle.net/HEDvf/663/ — user2481034, Sep 04 '13 at 07:26

score 3 · Answer 2 · answered Oct 04 '13 at 09:37

3

I have no reputation enough to add a command to the answer of @Gigo. So I will post this as a answer.

If you use European currency format, a point '.' is used as thousand separator instead of a comma ','. So the sorting script won't work correctly because 1.000,00 is interpreted as ONE point ZERO

To get this fixed, change the regex to:

/[^\d\-\,]/g

Point changed to comma, now 1.000,00 will be interpreted as ONE THOUSAND point ZERO.

answered Oct 04 '13 at 09:37

Timo002

3,138
4
40
65

Good point. If all numbers in the column are formatted identically this won't have any effect, I guess, but the regex should be adjusted to match the formatting, regardless. – Gigo Dec 29 '13 at 00:34

score 0 · Answer 3 · answered Oct 05 '21 at 06:55

Might be a dirty solution but you can use it to remove number format (,) from the string as well

jQuery.extend(jQuery.fn.dataTableExt.oSort, {
    "currency-pre": function (a) {
        console.log("PRE "+a);
        
        a = (a === "-") ? 0 : a.split(',').join('').replace(/[^\d\-\.]/g, "");
        return parseFloat(a);
    },
    "currency-asc": function (a, b) {
        console.log("ASC "+a);
        return a - b;
    },
    "currency-desc": function (a, b) {
        console.log("DESC "+a);
        return b - a;
    }
});

DataTables sort currency

3 Answers3