Filtering and sorting prices with space delimiter

mikemakus

Hi,

I have a "price" column in my datatable, which lists prices with a space every three decimals, for example "1 000 000". My problem is that if the user types "1000000" in the filter, it returns nothing.

Also the sorting function does not seem to like space separated numbers.

Is there an easy way to solve this ?

Thanks

allan

Hi mikemakus,

The problem you are facing here is that DataTables operates on exactly the string that you give it! So when filtering: "1 000 000" != 1000000. Likewise, with numeric sorting, numbers don't have spaces in them :-)

To overcome the sorting issue is fairly easy - just include the formatted numbers plug-in: http://datatables.net/plug-ins/sorting#formatted_numbers - and set the sType for the column.

The filtering is a little more complicated. There are a number of ways in which you can achieve what you want to. The easiest is probably custom filtering with a little plug-in that you develop: http://datatables.net/development/filtering . Then you could have a test against the input string with all the spaces stripped out.

Another option would be to render (fnRender) the column such that it has both strings in it: 1 000 000 - the downside to this method is that if you type 'span' it would match on that too! Unless you create a custom filter :-)

Hope this helps,
Allan

mikemakus

Thanks allan,

The formatted numbers plugin did the trick for the sorting.

For filtering I did what you suggested with the "span", it's not a problem (there are very few words containing "span" in french, the main language of my site) The problem is that it breaks the sorting with formatted numbers (probably because it doesn't strip of the html)

So what I did was to create a second column in the table, showing the original unformatted number and hiding it with css. The filter works, as it can still see this column, and the column sorting works too, as it has only the formatted number in it.