Qt_script_frmint

Definition

Language:	Qt Script
Plugin for language:	ScriptingQt
How to use:	Create custom SQL function, set it to "scalar" type. Suggested name: fmtInt
Function arguments	Keep undefined
Function usage:	SELECT fmtInt(colName) from tableName
Description:	Formats numbers like "123456789" into "123.456.789". When using frmInt() it is useful to change the character font for database results to a fixed width font such as "Lucida Sans Typewriter" or similar.
Author:	JrgMyr

Code

The code:

var t = arguments[0];
var lg = 13;

if (t > -9E9 && t < 1E10) {
    var t = '            ' + t; 
    t = t.substr(-lg);
    if (t.substr(lg-4, 1) >= '0')
        t = t.substr(1, lg-4) + '.' + t.substr(lg-3,3);
    if (t.substr(lg-8, 1) >= '0')
        t = t.substr(1, lg-8) + '.' + t.substr(lg-7,7);
    if (t.substr(lg-12, 1) >= '0')
        t = t.substr(1, lg-12) + '.' + t.substr(lg-11,11);
}

return t;

Example usage:

SELECT Company, Year, frmInt(COUNT(*)) AS LineItems, frmAmt(SUM(amount)) AS TotalAmount FROM datatable GROUP BY Company, Year;