CODEWITHSUNDEEP
javascriptjquerydatatablesgoogle-sheets
ukoku
ukoku

Reputation: 21

alphanumeric zero padding in dataTables?

I feel like this has a "duh" answer, but

I am using the jQuery Plugin DataTables.net to display a json Google Spreadsheet. You can see it in action here: http://jsfiddle.net/ukoku/jEqu2/2/

I would like to sort the rows by the Location column, which reads in A1, B2 format, like chess. With numbers I know I'd need zero padding, but I have no idea how to add zeros to alphanumeric data, nor do I have any clue how to do it with DataTables instead of regular string.

EDIT: Sorry, I wasn't clear. The place where I thought zeros needed to be added was between the alphabetic character and the number in the Location slot. Currently, Q9 sorts as being after Q14, for example. I need it to sort first by alpha (Ax, Bx), then within that sort as numbers. Normally when I've had 1 show up as higher than 10, it's because I needed to padleft with zeros to get 001 and 010. But I'm not sure how to stick zeros between the numbers for Q009 and Q014, etc., or it that's even the proper solution.

Eventually, the goal is to customize the CSS to display rows with the same location in groups. I honestly have no idea if this is possible either, but since I can't even get the data sorted....

Upvotes: 2

Views: 1071

Answers (2)

pete
pete

Reputation: 25081

Here's another way to do it:

var compare = function(a, b) {
        return a > b ? 1 : a === b ? 0 : -1;
    },
    compareAlphanumeric = function (a, b) {
        alpha = compare(a.alpha, b.alpha);
        numeric = compare(a.numeric, b.numeric);
        return (alpha === 1 | (alpha === 0 && numeric === 1)) ? 1 : (alpha === 0 && numeric === 0) ? 0 : -1;
    };
jQuery.fn.dataTableExt.oSort['alphaNumeric-asc'] = function(a, b) {
    var r = /^([A-Za-z]+)([0-9]+$)/,
        a = {
            "alpha": a.split(r)[1],
            "numeric": parseInt(a.split(r)[2], 10)
        },
        b = {
            "alpha": b.split(r)[1],
            "numeric": parseInt(b.split(r)[2], 10)
        };
        return compareAlphanumeric(a, b);
};
jQuery.fn.dataTableExt.oSort['alphaNumeric-desc'] = function(a, b) {
    var r = /^([A-Za-z]+)([0-9]+$)/,
        a = {
            "alpha": a.split(r)[1],
            "numeric": parseInt(a.split(r)[2], 10)
        },
        b = {
            "alpha": b.split(r)[1],
            "numeric": parseInt(b.split(r)[2], 10)
        };
        return compareAlphanumeric(b, a); //reverse a and b for desc
};

Working fiddle here: http://jsfiddle.net/jEqu2/3/

The compare function is your basic comparison function. compareAlphanumeric returns 1, 0, or -1 based on the following truth table:

/*
        Alpha   Numeric     Result
        -1      -1          -1
        -1      0           -1
        -1      1           -1
        0       -1          -1
        0       0           0
        0       1           1
        1       -1          1
        1       0           1
        1       1           1
*/

The bulk of the actual work is done in the oSort functions:

//First declare a RegExp to split the location into an array of [letter, number] 
var r = /^([A-Za-z]+)([0-9]+$)/,
//convert the passed "a" parameter to an object containing its letter and number,
//and parse the number portion to an actual number instead of its string representation.
    a = {
        "alpha": a.split(r)[1],
        "numeric": parseInt(a.split(r)[2], 10)
    },
//do the same for b
    b = {
        "alpha": b.split(r)[1],
        "numeric": parseInt(b.split(r)[2], 10)
    };
//return the alphanumeric comparison
    return compareAlphanumeric(a, b);

and in the descending oSort, we only have to switch the order of the parameters passed to compareAlphanumeric.

Upvotes: 0

gllen
gllen

Reputation: 66

Is this on the right track of what you're after? http://jsfiddle.net/rMUWD/

Upvotes: 2

Related Questions