datatable sorting on string as number

Question

i have datatable and i want to sort in as numeric it contains value like 1st,2nd...., here is my code when i sort it it sorts values like 1st,10th,2nd so on how to sort it properly?

$('#example').DataTable( {
   //      "columnDefs": [
   //   { "visible": false, "targets": 4 }
   // ],
"aaSorting": [[1,'asc']],
   "columnDefs": [ {
    "targets": [2,5,6],
    "orderable": false
  } ,
  {
    "targets": 0,
    "orderable": false
  },
  { "width": "5%", "targets": 0 },
  { "width": "8%", "targets": 1 }],

  initComplete: function () {

    this.api().columns().every( function () {
      var column = this;
      var select = $('<select><option value=""></option></select>')
      .appendTo( $(column.footer()).empty() )
      .on( 'change', function () {
        var val = $.fn.dataTable.util.escapeRegex(
         $(this).val()
         );

        column
        .search( val ? '^'+val+'$' : '', true, false )
        .draw();
      } );

      column.data().unique().sort().each( function ( d, j ) {
        select.append( '<option value="'+d+'">'+d+'</option>' )
      } );
    } );
  }
});

score 16 · Answer 1 · answered Feb 22 '18 at 16:47

I suggest using orthogonal data&HTML 5 in DataTable. It is simple and good solution.

It is simple solution, because it doesn't needs any configuration change or additional coding.

And it is good solution, because it separates sorting values from data representation. So you can show anything to user and sort by values as you would like to.

In each td element there should be data-order attribute. For an example:

<td data-order="3120">$3,120/m</td>

More about this https://datatables.net/manual/data/orthogonal-data

score 11 · Accepted Answer · edited Jun 20 '20 at 09:12

The simplest way I know of to do this is to use the Formatted Numbers plugin

Here is an example:

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

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

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.10/js/jquery.dataTables.min.js"></script>
<link href="https://cdn.datatables.net/1.10.10/css/jquery.dataTables.min.css" rel="stylesheet"/>
<table id="tbl_jaar">
  <thead>
    <tr>
      <th>Places</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1st</td>
    </tr>
    <tr>
      <td>2nd</td>
    </tr>    
    <tr>
      <td>3rd</td>     
    </tr>   
    <tr>
      <td>4th</td>
    </tr>
    <tr>
      <td>5th</td>
    </tr>
    <tr>
      <td>6th</td>
    </tr>
    <tr>
      <td>7th</td>
    </tr>
    <tr>
      <td>8th</td>
    </tr>
    <tr>
      <td>9th</td>
    </tr>
    <tr>
      <td>10th</td>
    </tr>
  </tbody>
</table>

score 3 · Answer 3 · answered Oct 22 '16 at 06:25

you need to define sType as numeric on the columndef where you want sorting as number

$('#example').DataTable( {
   "aoColumns": [
      { "sType": "numeric" },
      null,
      null,
      null,
      null
    ],
// define at the place where sorting should by by numeric
// other options goes here
});

// with above the column at index 0 will be sorted by numeric and other columns are normal auto detected. the length of aoColumns should be equal to number of columns.

score 0 · Answer 4 · answered Nov 29 '21 at 13:36

$.extend($.fn.dataTable.ext.oSort, {
    "numNonStandard-asc": function (a, b) {

      if(!isNaN(parseInt(a.substring(1)))){
        a = parseInt(a.substring(1));
      }
      if(!isNaN(parseInt(b.substring(1)))){
        b = parseInt(b.substring(1));
      }
      return (a == b) ? 0 : (a < b) ? 1 : -1;
    },
    "numNonStandard-desc": function (a, b) {
      if(!isNaN(parseInt(a.substring(1)))){
        a = parseInt(a.substring(1));
      }
      if(!isNaN(parseInt(b.substring(1)))){
        b = parseInt(b.substring(1));
      }
      return (a == b) ? 0 : (a < b) ? -1 : 1;
    }
  });

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Nov 29 '21 at 14:18

datatable sorting on string as number

4 Answers4

Here is an example:

Linked

Related