How to always keep a number with 2 decimal places?-CodePudding

I'm trying to keep the numbers in a column in sqlite to have two decimal places after the comma. If there is only one number after the decimal point, then I must add 0. Example:

drop table if exists numbers;
create table numbers (number float);

insert into numbers values ('97.516,82');
insert into numbers values ('97.409,9');
insert into numbers values ('97.409,87');
insert into numbers values ('97.331,6'); -- correct 97.331,60
insert into numbers values ('97.229,00');
insert into numbers values ('97.183,70');
insert into numbers values ('97.163,25');
insert into numbers values ('97.163,1'); -- correct 97.163,10
insert into numbers values ('97.121,98');
insert into numbers values ('97.105,56');
insert into numbers values ('97.101,44');
insert into numbers values ('97.076,77');
insert into numbers values ('97.060,28');
insert into numbers values ('97.019,17');
insert into numbers values ('97,34');
insert into numbers values ('97,3'); -- correct 97,30
insert into numbers values ('969,19');
insert into numbers values ('968,89');
insert into numbers values ('968,7'); -- correct 968,70
insert into numbers values ('968,76');

select * from numbers

I commented the way it should be formatted, I already tried the following

select printf('%.2f',number) from numbers

But it stays in this format that I don't want Can anyone help?

CodePudding user response：

printf('%.2f',number) is the correct way to always display two decimal places, but the problem is in the INSERT not in the query.

To get the correct result in the query you need to correct the values you insert.

Sqlite has Flexible Data Typing. Since you declared the column as FLOAT, it will try to convert any value you try to insert to an INTEGER or REAL (in order of preference). Since in every value a , character is present, Sqlite will interpret it as a string and store it as a TEXT value.

When you try to convert to a floating point with printf('%.2f',number), the point is interpreted as decimal separator and anything after a comma , is discarded, so the first value you insert is converted to 97.516 (which is then rounded by printf('%.2f',number) to 97.52) while the last is converted to 968 (and printed as 968.00).

printf('%,.2d.d',replace(replace(number,'.',''),',','.') is a good way to do this, but you can't add thousands separators. To add them you need to concatenate the integer part and the decimal part as if they where two integers:

select printf('%,d.d',replace(replace(number,'.',''),',','.'),replace(replace(number,'.',''),',','.')*1000) from numbers

The first value will be converted to an integer with thousand separators, then a point and then the second number (the first two digits of the decimal parts treated as an integer).

CodePudding user response：

Step 1: Identify the digit at the thousandths place or the third place to the right of the decimal point. Step 2: If the thousandths place digit is greater than or equal to 5, the hundredths place digit will be increased by one. And if the thousandths place digit is less than 5, the hundredths place digit will remain unchanged. Step 3: Ignore all the remaining digits in the decimal part of the number. Look at the image given below showing the rounding up value of a decimal number 2.835706 up to two decimal places.

round to two decimal places

Let us take one more example of round to two decimal places to understand this concept better. Round 5.803 to two decimal places.

To do this, let us follow the above steps. The first step is to identify the thousandths place digit which is 3 in this number. Now, since 3<5, we have to keep the digit at the hundredths place unchanged. Therefore, 5.803 ≈ 5.80 (where ≈ is the symbol for approximation).

Use Cuemath's free online round to two decimal places calculator to verify your answers.