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>' )
      } );
    } );
  }
}); 

Solution 1

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>

Solution 2

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

Solution 3

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.