How to sort a number with thousands separator

Question

I have tried to sort the number with jQuery Datatables plug-in but is not working with C# string number formats.

I have tried:

decimal value= 12345678.00
value..ToString("#,##.00");
value.ToString("##,##.##");
value.ToString("0,0.00", CultureInfo.InvariantCulture)

but no luck because of the comma. If there is no comma is works fine or all the numbers with same count also working i.e.

01,121,372.01

02,002,009.22

11,222,222,33

If it is as below then it is not working

1,111,111.11

222,191.00

32,222.00

Then try to turn the string into a number. C# doesn't have any special rules for sorting with numbers in a string, it just sorts it based on alpha-numeric characters. The only way you can get accurate sorting is if you store it as a number truly within the datatable then sort on it and display the string value after sort. — Mark Fitzpatrick, Jul 08 '15 at 23:43

score 1 · Answer 1 · answered Jul 14 '15 at 14:14

I have done like this to overcome to this issue.

"aoColumnDefs": [ {
                    "aTargets": [3,4,6],
                    "fnCreatedCell": function (nTd, sData, oData, iRow, iCol) {
                        var $currencyCell = $(nTd);
                        var commaValue = $currencyCell.text().replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,");
                        $currencyCell.text(commaValue);
                    }
                }]

score 0 · Answer 2 · answered Jul 13 '15 at 16:41

For DataTables 1.10

DataTables 1.10+ has formatted number detection and sorting abilities built- in, there is no extra coding needed.

Alternatively you can set columns.type to num-fmt to force specific data type.

See the example below for demonstration.

$(document).ready(function() {
  $('#example').dataTable();

});

<!DOCTYPE html>
<html>

<head>
<meta charset="ISO-8859-1">

<link href="//cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" rel="stylesheet" />
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.3.0/css/font-awesome.min.css" rel="stylesheet" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script src="//cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>

  </head>
<body>
<table id="example" class="display" cellspacing="0" width="100%">

  <thead>
    <tr>
      <th>Name</th>
      <th>Position</th>
      <th>Office</th>
      <th>Age</th>
      <th>Start date</th>
      <th>Salary</th>
    </tr>
  </thead>

  <tfoot>
    <tr>
      <th>Name</th>
      <th>Position</th>
      <th>Office</th>
      <th>Age</th>
      <th>Start date</th>
      <th>Salary</th>
    </tr>
  </tfoot>

  <tbody>
    <tr>
      <td>Tiger Nixon</td>
      <td>System Architect</td>
      <td>Edinburgh</td>
      <td>61</td>
      <td>2011/04/25</td>
      <td>111,111.11</td>
    </tr>
    <tr>
      <td>Garrett Winters</td>
      <td>Accountant</td>
      <td>Tokyo</td>
      <td>63</td>
      <td>2011/07/25</td>
      <td>222,191.00</td>
    </tr>
    <tr>
      <td>Ashton Cox</td>
      <td>Junior Technical Author</td>
      <td>San Francisco</td>
      <td>66</td>
      <td>2009/01/12</td>
      <td>32,222.00</td>
    </tr>
  </tbody>
</table>
</body>
</html>

For DataTables 1.9

For older DataTables 1.9 you can use Formatted numbers sorting plug-in.

You just need to include this JS file: //cdn.datatables.net/plug-ins/1.10.7/sorting/formatted-numbers.js and use the code below to set data type to formatted number.

$('#example').dataTable({
   columnDefs: [
     { type: 'formatted-num', targets: 0 }
   ]
});

@Juan, there is a confusion I believe. The question is about jQuery DataTables plug-in as OP noted in the comments, not .NET class `DataTable`. Although the formatting problem could be solved on the server-side as well with C#, there are downsides since the number would still need to be formatted on the client-side. — Gyrocode.com, Jul 13 '15 at 17:41

How to sort a number with thousands separator

2 Answers2

Linked