How to sort numbers and text in the same column Javascript

Question

I have a table that uses DataTables. The table consists of 4 columns and has text in some and numbers in some as well. Within the numbers column if there is no number then 'null' is shown in the td. How can I still sort the numbers correctly and give the null value a 0 or some number to help sort better?

Right now when you sort through the list it's not sorting more than one digit. So '10' comes before '3'. Also, do you notice the 1440 comes before 180.

You can view my http://codepen.io/tetonhiker/pen/dOBeqY

$(function() {
  $('#dataTable').DataTable({
    "paging": false,
    "info": false
  });
});

<link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons">
<link rel="stylesheet" href="https://code.getmdl.io/1.3.0/material.indigo-pink.min.css">
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.13/css/jquery.dataTables.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<script src="https://code.getmdl.io/1.3.0/material.min.js"></script>
<script src="https://cdn.datatables.net/1.10.13/js/jquery.dataTables.js"></script>


<table id="dataTable" class="mdl-data-table mdl-js-data-table mdl-shadow--2dp dataTable">
  <thead>
    <tr role="row">
      <th class="mdl-data-table__cell--non-numeric">Shape Name</th>
      <th class="numeric-cell">Number Edges</th>
      <th class="numeric-cell">Sum of Interior Angles</th>
      <th class="mdl-data-table__cell--non-numeric">Deleted?</th>
    </tr>
  </thead>
  <tbody>
    <tr class="rowEditData odd" value="7924" role="row" title="">
      <td class="mdl-data-table__cell--non-numeric">Hexagon</td>
      <td class="numeric-cell">6</td>
      <td class="numeric-cell">null</td>
      <td class="mdl-data-table__cell--non-numeric">No</td>
    </tr>
    <tr class="rowEditData odd deleted" value="7930" role="row" title="">
      <td class="mdl-data-table__cell--non-numeric">null</td>
      <td class="numeric-cell">3</td>
      <td class="numeric-cell">180</td>
      <td class="mdl-data-table__cell--non-numeric">No</td>
    </tr>
    <tr class="rowEditData even" value="7931" role="row">
      <td class="mdl-data-table__cell--non-numeric">null</td>
      <td class="numeric-cell">4</td>
      <td class="numeric-cell">360</td>
      <td class="mdl-data-table__cell--non-numeric">No</td>
    </tr>
    <tr class="rowEditData odd" value="7932" role="row" title="">
      <td class="mdl-data-table__cell--non-numeric">null</td>
      <td class="numeric-cell">5</td>
      <td class="numeric-cell">540</td>
      <td class="mdl-data-table__cell--non-numeric">No</td>
    </tr>
    <tr class="rowEditData even" value="7933" role="row">
      <td class="mdl-data-table__cell--non-numeric">null</td>
      <td class="numeric-cell">6</td>
      <td class="numeric-cell">120</td>
      <td class="mdl-data-table__cell--non-numeric">No</td>
    </tr>
    <tr class="rowEditData odd" value="7934" role="row">
      <td class="mdl-data-table__cell--non-numeric">null hello</td>
      <td class="numeric-cell">10</td>
      <td class="numeric-cell">1440</td>
      <td class="mdl-data-table__cell--non-numeric">No</td>
    </tr>
    <tr class="rowEditData even" value="7925" role="row">
      <td class="mdl-data-table__cell--non-numeric">Octagon sample</td>
      <td class="numeric-cell">8</td>
      <td class="numeric-cell">null</td>
      <td class="mdl-data-table__cell--non-numeric">No</td>
    </tr>
    <tr class="rowEditData odd" value="7922" role="row">
      <td class="mdl-data-table__cell--non-numeric">pentagon</td>
      <td class="numeric-cell">null</td>
      <td class="numeric-cell">null</td>
      <td class="mdl-data-table__cell--non-numeric">No</td>
    </tr>
    <tr class="rowEditData even deleted" value="7926" role="row">
      <td class="mdl-data-table__cell--non-numeric">Pentagon</td>
      <td class="numeric-cell">null</td>
      <td class="numeric-cell">null</td>
      <td class="mdl-data-table__cell--non-numeric">No</td>
    </tr>
    <tr class="rowEditData odd" value="7920" role="row">
      <td class="mdl-data-table__cell--non-numeric">square-test</td>
      <td class="numeric-cell">4</td>
      <td class="numeric-cell">null</td>
      <td class="mdl-data-table__cell--non-numeric">No</td>
    </tr>
    <tr class="rowEditData even" value="7927" role="row">
      <td class="mdl-data-table__cell--non-numeric">Square</td>
      <td class="numeric-cell">null</td>
      <td class="numeric-cell">null</td>
      <td class="mdl-data-table__cell--non-numeric">No</td>
    </tr>
  </tbody>
</table>

lostInTheTetons · Accepted Answer · 2016-12-29 17:26:42Z

Try out this sorting plugin for DataTables: https://datatables.net/plug-ins/sorting/natural

(function() {

/*
 * Natural Sort algorithm for Javascript - Version 0.7 - Released under MIT     license
 * Author: Jim Palmer (based on chunking idea from Dave Koelle)
 * Contributors: Mike Grier (mgrier.com), Clint Priest, Kyle Adams, guillermo
 * See: http://js-naturalsort.googlecode.com/svn/trunk/naturalSort.js
 */
function naturalSort (a, b, html) {
var re = /(^-?[0-9]+(\.?[0-9]*)[df]?e?[0-9]?%?$|^0x[0-9a-f]+$|[0-9]+)/gi,
    sre = /(^[ ]*|[ ]*$)/g,
    dre = /(^([\w ]+,?[\w ]+)?[\w ]+,?[\w ]+\d+:\d+(:\d+)?[\w ]?|^\d{1,4}[\/\-]\d{1,4}[\/\-]\d{1,4}|^\w+, \w+ \d+, \d{4})/,
    hre = /^0x[0-9a-f]+$/i,
    ore = /^0/,
    htmre = /(<([^>]+)>)/ig,
    // convert all to strings and trim()
    x = a.toString().replace(sre, '') || '',
    y = b.toString().replace(sre, '') || '';
    // remove html from strings if desired
    if (!html) {
        x = x.replace(htmre, '');
        y = y.replace(htmre, '');
    }
    // chunk/tokenize
var xN = x.replace(re, '\0$1\0').replace(/\0$/,'').replace(/^\0/,'').split('\0'),
    yN = y.replace(re, '\0$1\0').replace(/\0$/,'').replace(/^\0/,'').split('\0'),
    // numeric, hex or date detection
    xD = parseInt(x.match(hre), 10) || (xN.length !== 1 && x.match(dre) && Date.parse(x)),
    yD = parseInt(y.match(hre), 10) || xD && y.match(dre) && Date.parse(y) || null;

// first try and sort Hex codes or Dates
if (yD) {
    if ( xD < yD ) {
        return -1;
    }
    else if ( xD > yD ) {
        return 1;
    }
}

// natural sorting through split numeric strings and default strings
for(var cLoc=0, numS=Math.max(xN.length, yN.length); cLoc < numS; cLoc++) {
    // find floats not starting with '0', string or 0 if not defined (Clint Priest)
    var oFxNcL = !(xN[cLoc] || '').match(ore) && parseFloat(xN[cLoc], 10) || xN[cLoc] || 0;
    var oFyNcL = !(yN[cLoc] || '').match(ore) && parseFloat(yN[cLoc], 10) || yN[cLoc] || 0;
    // handle numeric vs string comparison - number < string - (Kyle Adams)
    if (isNaN(oFxNcL) !== isNaN(oFyNcL)) {
        return (isNaN(oFxNcL)) ? 1 : -1;
    }
    // rely on string comparison if different types - i.e. '02' < 2 != '02' < '2'
    else if (typeof oFxNcL !== typeof oFyNcL) {
        oFxNcL += '';
        oFyNcL += '';
    }
    if (oFxNcL < oFyNcL) {
        return -1;
    }
    if (oFxNcL > oFyNcL) {
        return 1;
    }
}
return 0;
}

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
"natural-asc": function ( a, b ) {
    return naturalSort(a,b,true);
},

"natural-desc": function ( a, b ) {
    return naturalSort(a,b,true) * -1;
},

"natural-nohtml-asc": function( a, b ) {
    return naturalSort(a,b,false);
},

"natural-nohtml-desc": function( a, b ) {
    return naturalSort(a,b,false) * -1;
},

"natural-ci-asc": function( a, b ) {
    a = a.toString().toLowerCase();
    b = b.toString().toLowerCase();

    return naturalSort(a,b,true);
},

"natural-ci-desc": function( a, b ) {
    a = a.toString().toLowerCase();
    b = b.toString().toLowerCase();

    return naturalSort(a,b,true) * -1;
}
} );

}());

Andy Ray · Accepted Answer · 2016-12-29 14:45:30Z

0

You have the class "non-numeric" in each row, which tells this weird plugin not to sort those columns as numbers. Remove that string.

answered Dec 29, 2016 at 14:45

Andy Ray

32.3k16 gold badges114 silver badges151 bronze badges

3 Comments

JBaldwin Over a year ago

That still doesn't fix the sorting issue. When I remove the classes it still sorts wrong with 10 being the first number then 3 and so one. The nulls are placed at the end of the list or at the top.

Andy Ray Over a year ago

hmm. if you replace the nulls with zeroes it works. you probably need to define a custom sorting function. Also note your codepen errors because you include the plugin before jquery

JBaldwin Over a year ago

How could I replace the nulls with zeros? Also, I updated my codepen code as well. Still running into the same issues.

Collectives™ on Stack Overflow

How to sort numbers and text in the same column Javascript

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related