Como filtrar una columna donde hay valores de dos y tres cifras

Driver1964

Buenas tardes.
Tengo una tabla con valores en milímetros en la columna B (20 mm. 50 mm. 100.)
Cuando intento filtrar por valor de menor a mayor o viceversa no me lo hace adecuadamente.
Lo único que puedo hacer para que los filtre bien es poner un cero a la izquierda de las cifras, pero no me gusta como queda estéticamente (020 mm. 050 mm. 100 mm.)
Como se podría solucionar

Driver1964

Este es un ejemplo de mi tabla.https://live.datatables.net/meqadive/1/edit

rf1234

Here is some code that should help you:
$.fn.dataTable.ext.type.detect.unshift is used to detect columns with formatted numbers - like your mm column. You would need to specify what the additional content is. In your case it is "mm", but you could also use this with currencies, e.g. "EUR". Just add the extra content to the "addContent" array.

$.extend( $.fn.dataTable.ext.type.order makes sure the additional content of formatted numbers gets stripped off. This will also work with formatted Spanish numbers such as 1.000.000,00 EUR for 1M Euros - if you add "EUR" to the array above.

The code must be executed BEFORE data table initialization.

var addContent = [ 'mm.' ];

//auto detection of fields with formatted numbers
$.fn.dataTable.ext.type.detect.unshift( function ( data ) {
    if (typeof data !== 'undefined') {
        if ( data != null )  {
            var i=0;
            while ( addContent[i] ) {
                if ( isNaN(data) ) {  //search may only be performed on non-numeric fields
                    if ( data.search( addContent [i] ) > -1 )   {
                        return 'formatted-num';
                    }
                } else { //for pure numbers we want the same sorting as well!!
                    return 'formatted-num';
                }
                i++;
            }
            if ( data === '') {
                return 'formatted-num';
            }     
        }
    }
    return null;
} );

//sorting of formatted numbers in Spanish format
$.extend( $.fn.dataTable.ext.type.order, { 
    "formatted-num-pre": function ( a ) {
        a = a.toString().replace( /[\.]/g, "" );
        a = a.toString().replace( /[\,]/g, "." );
        a = a.toString().replace( /[^\d.-]/g, "" );
        a = parseFloat(a);
        if ( ! isNaN(a) ) {
            return a;
        } else {
//14 digit negative number to make sure empty cells always stay at the bottom / top
            return -99999999999999;
        }
    },
    "formatted-num-asc": function ( a, b ) {
            return a - b;
    },
    "formatted-num-desc": function ( a, b ) {
            return b - a;
    }
} );

This is a working example:
https://live.datatables.net/tocedido/1/edit

allan

Nice one - thanks @rf1234, as always!

One thing I'd like to point out is that as of v2 the preferred way to interact with types is the DataTable.types() method - that includes sorting and type detection. The manual has more details. The old ways still work, and the new API is basically a layer on top of them, but the new way is better documented and typed.

Allan

Driver1964

Thank you so much @rf1234

Driver1964

@rf1234
He colocado tu código en distintos lugares de mi código html y no logro que funcione.
Te agradecería que me indicaras donde he de colocar el código exactamente en el ejemplo que he subido.

Muchísimas gracias

rf1234

Te agradecería que me indicaras donde he de colocar el código exactamente en el ejemplo que he subido.

Well it is in there already. I posted the working example above.
But here it is again:
https://live.datatables.net/tocedido/1/edit

It is on the JavaScript page, separate from the HTML. The code needs be executed before
$('#table_id').DataTable();

Driver1964

@rf1234

Ahora sí que ha funcionado.
Muchísimas gracias de nuevo

rf1234

De nada!

Now I filled out your "precio" column with a price in Spanish number formatting for prices. That works too if the key word "EUR" is included in the column text.

You could also use this without the key word "EUR" or any other currency, but then you run the risk that other columns that are not to be detected as formatted numbers (e.g. because they are dates in a format like DD.MM.YYYY), are indeed detected as the former.

https://live.datatables.net/borexagi/1/edit

Driver1964

Ok. I understand.

Thanksssss