Sqlite Format Number with 2 Decimal Places Always

How to format a Float number in SQLite?

The following is one way that you could accomplish the result:-

select *, printf ("U$%.2f", CAST(unit_val AS FLOAT) / 100) AS u_val FROM items;

Could become :-

SELECT 
*, 
CASE
    WHEN len < 9 THEN myfloat 
    WHEN len> 8  AND len < 12 THEN substr(myfloat,1,len - 6)||','||substr(myfloat,len - 5)
    WHEN len > 11 AND len < 15 THEN substr(myfloat,1,len -9)||','||substr(myfloat,len-8,3)||','||substr(myfloat,len-5)
    WHEN len > 14 AND len < 18 THEN substr(myfloat,1,len - 12)||','||substr(myfloat,len -11,3)||','||substr(myfloat,len-8,3)||','||substr(myfloat,len-5)
END AS u_val
FROM   
    (
        SELECT *, length(myfloat) AS len
        FROM
            (
                SELECT *,printf("U$%.2f",CAST(unit_val AS FLOAT) / 100)) AS myfloat
                FROM Items
             )
    )

The innermost SELECT extracts the original data plus a new column as per your orginal SELECT.

The intermediate SELECT adds another column for the length of the new column as derived from unit_val via the printf (this could have been done in the first/innermost SELECT, getting this value simplifies (in my opinion) the outermost SELECT, or you could use multiple length(myfloats) in the outermost SELECT).

RESULT - Example

The following is the result from a testing (source column is myfloat) :-

• The highlighted columns being the original columns.
• The circled data being the result.
• The other 2 columns are intermediate columns.

Edit

As you've clarified that the input is an integer, then :-

SELECT *,'U$'||printf('%,d',(unit_val/100))||'.'||CAST((unit_val % 100) AS INTEGER) AS u_val FROM Items

would work assuming that you are using at least version 3.18 of SQLite.

Correction

Using the SQL immediately above if the value of the last part (the cents) is less than 10 then the leading 0 is dropped. So the correct SQL is. Note for simplicity the CAST has also been dropped and rather than concatening the . it has been added to the printf formatter string so :-

SELECT 
    'U$' || 
    printf('%,d', (unit_val / 100)) ||
    printf('.%02d',unit_val % 100) 
    AS u_val
FROM Items

Or as a single line

SELECT 'U$' || printf('%,d', (unit_val / 100)) || printf('.%02d',unit_val % 100) AS u_val FROM Items

How to set 2 decimal places in sqlite select statement

SELECT _id, name, ROUND(amount,2) FROM accounts

Following code will give you idea how you can do it

    

public class TestListView extends ListActivity {
  ...
  private DecimalFormat myCustDecFormatter = new DecimalFormat("########.00");
  ...
  ...
  /** Called when the activity is first created. */
  @Override
  public void onCreate(Bundle savedInstanceState)  {
    ...
    ...
    ...
  }

private void fillData() {
    /* Get all of the rows from the database and create the item list */
    /* for mult accts, pass in acct name? */
    mEntryCursor = mDbHelper.fetchAllEntries();
    startManagingCursor(mEntryCursor);

    // Create an array to specify the fields we want to display in the list (only TITLE)
    String[] from = new String[]{myDbAdapter.KEY_NMBR,myDbAdapter.KEY_DATE,myDbAdapter.KEY_DESCR,myDbAdapter.KEY_AMT};

    // and an array of the fields we want to bind those fields to (in this case just text1)
    int[] to = new int[]{R.id.txtnmbr, R.id.txtdate, R.id.txtdescr, R.id.txtamt};

    // Now create a simple cursor adapter and set it to display
    setListAdapter(new SimpleCursorAdapter(this, R.layout.entryrow, mEntryCursor, from, to) {
        @Override
        public void setViewText(TextView v, String text) {
          super.setViewText(v, convText(v, text));
        }

    });

  }

  private String convText(TextView v, String text) {
    switch (v.getId()) {
      case R.id.txtamt:
        double dblAmt;
        //dblAmt = Double.valueOf(text);
        dblAmt = mEntryCursor.getDouble(AMT_COLUMN);
        return myCustDecFormatter.format(dblAmt);
    }
      return text;
    } 

}//end TestListView

Format to 2 decimal places for textview value from SQLite

Format your String before setting it to the TextView:

display.setText(String.format("%.02f",cursor.getDouble(1)));

SQL Cannot Format Numbers and Decimal (Always Automatically Rounding Up)

Simply:

SELECT FORMAT(123456.68,2);

Parameters : This function generally accepts three parameters.

N – The number which is to be formatted.

D – The number of decimal
places to which the number is round off.

locale – It’s an optional
parameter, which decides a thousand separators and grouping between
separators. By default, en_US locale is present in MySQL.

Returns :
The function formats the given number, round off it to a certain
decimal place, and return the number in the form of string.

Check for more info: https://www.geeksforgeeks.org/format-function-in-mysql/

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4aa3cbf11cc05f2e1accc4fa6bcd20a1

Use Format DE_DE and infinite decimal places in SQL query results

If you don't specify number of decimal places to round the value to, it takes the default 0 which means no decimal places at all. I don't think you need infinite number as you've mentioned just because you can have 3 decimal places maximum as per your definition Costs_Outbound DECIMAL(65,3).

That said 3 seems like a number you're looking for.

SELECT country,
FORMAT(sum(Costs_Outbound), 3 ,'DE_DE') Costs_Outbound
FROM Logistics
GROUP BY country WITH ROLLUP

If you need more (for example 255), you should consider altering your column data type and putting the same number into FORMAT(X, D, [locale]) as D argument - see below. Also, to get rid of trailing zeros you could use TRIM:

TRIM(TRAILING '0' FROM FORMAT(sum(Costs_Outbound), 255 ,'DE_DE')) Costs_Outbound

Decimal places problem with SQLite

SQLite internally uses IEEE binary floating point arithmetic, which truly does not lend itself well to maintaining a particular number of decimals. To get that type of decimal handling would require one of:

1. Fixed point math, or
2. IEEE decimal floating point (rather uncommon), or
3. Handling everything as strings.

Formatting the values (converting from floating point to string) after extraction is the simplest way to implement things. You could even hide that inside some sort of wrapper so that the rest of the code doesn't have to deal with the consequences. But if you're going to do arithmetic on the value afterwards then you're better off not formatting and instead working with the value as returned by the query, because the format and reconvert back to binary floating point (which Python uses, just like the vast majority of other modern languages) loses lots of information in the reduced precision.

---

Related Topics