DataTables sort mixed numeric and text column

Question

I am trying to construct a DataTable sortable table that contains a column of numbers, but this column also contains the text NA to denote missing data. I was wondering how I would write a custom sorting function so that when I sort on the column the NAs come up AFTER the numbers rather than before when listing values greatest to smallest. Codepen here: https://codepen.io/mayagans/pen/VwKJeMo

$('#my-table').DataTable({
            "autoWidth": false,
            "order": []
        });

td {
  padding: 0 20px;
}

table {
  margin: 10px 0;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<table id="my-table">
    <thead>
        <th>Nr. </th>
        <th>Name</th>
    </thead>
    <tr>
        <td>1</td>
        <td>Carl</td>
    </tr>
    <tr>
        <td>2</td>
        <td>Alan</td>
    </tr>
    <tr>
        <td>3</td>
        <td>Bobby</td>
    </tr>
      <tr>
        <td>NA</td>
        <td>Maya</td>
    </tr>
        <tr>
        <td>NA</td>
        <td>Jordan</td>
    </tr>
      </tr>
        <tr>
        <td>-</td>
        <td>Monica</td>
    </tr>
</table>

Desired Output:

When sorting from largest to smallest value:

<table id="my-table">
    <thead>
        <th>Nr. </th>
        <th>Name</th>
    </thead>
    <tr>
        <td>3</td>
        <td>Bobby</td>
    </tr>
        <tr>
        <td>2</td>
        <td>Alan</td>
    </tr>
        <tr>
        <td>1</td>
        <td>Carl</td>
    </tr>
      <tr>
        <td>NA</td>
        <td>Maya</td>
    </tr>
        <tr>
        <td>NA</td>
        <td>Jordan</td>
    </tr>
      </tr>
        <tr>
        <td>-</td>
        <td>Monica</td>
    </tr>
</table>

Any help appreciated!!

andrewJames · Accepted Answer · 2021-01-24 17:22:51Z

2

You can take advantage of orthogonal data, which lets you store a custom sorting value, which is different from the display value:

$('#my-table').DataTable( {
  columnDefs: [ { 
    targets: [ 0 ], 
    render: function ( data, type, row ) {
      if ( type === 'sort' ) {
        var sortValue = data;
        switch( data ) {
          case '-':
            sortValue = -999998;
            break;
          case 'NA':
            sortValue = -999999
            break;
          default: // already set, in this case
        } 
        return sortValue;
      } else { 
        return data;
      }
  }
  } ]
} );

This assumes you will never have numeric values lower than the two values used in the switch statement. You can adjust as needed, if your overall data is not compatible with this.

My sample:

answered Jan 24, 2021 at 17:22

andrewJames

22.6k11 gold badges32 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

DataTables sort mixed numeric and text column

Desired Output:

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Desired Output:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related