Currency Format for different countries US,German,French

yenkumar

There are around 3 types of currrency formats as shown below.

* US Format - 1,234,567.89
* German Format - 1.234.567,89
* French Format - 1 234 567,89

So sorting these type of things in datatable had been very difficult. But i figured it out and want to share the functionality.
jQuery.fn.dataTableExt.oSort['formatted-currency-asc'] = function(a,b){
a = a.replace(" ","").replace(" ","");
b = b.replace(" ","").replace(" ","");

if(a.match(/^(\d{1,3}(\,\d{3})*|(\d+))(\.\d{2})?$/)) //US Format
{
var aa = parseFloat(a.replace(/[^0-9.-]/g,''));
if (isNaN(aa)) aa = 0;
}
else if(a.match(/^(\d{1,3}(\\d{3})*|(\d+))(\,\d{2})?$/)) //France Format
{
var aa = (a == "-") ? 0 : a.replace( /\./g, "" ).replace( /,/, "." );
aa = parseFloat( aa );
}
else // German Format
{
var aa = (a == "-") ? 0 : a.replace( /\./g, "" ).replace( /,/, "." );
aa = parseFloat( aa );
}
if(b.match(/^(\d{1,3}(\,\d{3})*|(\d+))(\.\d{2})?$/))
{
bb = parseFloat(b.replace(/[^0-9.-]/g,''));
if (isNaN(bb)) bb = 0;
}
else if(b.match(/^(\d{1,3}(\\d{3})*|(\d+))(\,\d{2})?$/)) //France Format
{
var bb = (b == "-") ? 0 : b.replace( /\./g, "" ).replace( /,/, "." );
bb = parseFloat(bb);
}
else //German Format
{
var bb = (b == "-") ? 0 : b.replace( /\./g, "" ).replace( /,/, "." );
bb = parseFloat(bb);
}
return aa-bb;
}

jQuery.fn.dataTableExt.oSort['formatted-currency-desc'] = function(a,b){
a = a.replace(" ","").replace(" ","");
b = b.replace(" ","").replace(" ","");
if(a.match(/^(\d{1,3}(\,\d{3})*|(\d+))(\.\d{2})?$/)) //US Format
{
var aa = parseFloat(a.replace(/[^0-9.-]/g,''));
if (isNaN(aa)) aa = 0;
}
else if(a.match(/^(\d{1,3}(\\d{3})*|(\d+))(\,\d{2})?$/)) //France Format
{
var aa = (a == "-") ? 0 : a.replace( /\./g, "" ).replace( /,/, "." );
aa = parseFloat( aa );
}
else // German Format
{
var aa = (a == "-") ? 0 : a.replace( /\./g, "" ).replace( /,/, "." );
aa = parseFloat( aa );
}
if(b.match(/^(\d{1,3}(\,\d{3})*|(\d+))(\.\d{2})?$/))
{
bb = parseFloat(b.replace(/[^0-9.-]/g,''));
if (isNaN(bb)) bb = 0;
}
else if(a.match(/^(\d{1,3}(\\d{3})*|(\d+))(\,\d{2})?$/)) //France Format
{
var bb = (b == "-") ? 0 : b.replace( /\./g, "" ).replace( /,/, "." );
bb = parseFloat(bb);
}
else //German Format
{
var bb = (b == "-") ? 0 : b.replace( /\./g, "" ).replace( /,/, "." );
bb = parseFloat(bb);
}
return bb-aa;
}

allan

Very nice! Thanks for posting that :-). The only concern I would have is that there is a lot of regular expressions being used - not a bad thing in and of itself, but it will slow the browser down significantly when sorting a large table, since it will need to work out the currency being used every time. This will be needed if the three formats are mixed in one column - but if they are independent, it would increase speed to use an type detection plug-in to decide which of the three it is, and then do the sort part as quickly as possible.

Allan