1
create table salesman(
salesman_id number(6), 
commission(5,2)
);

insert into salesman(salesman_id, 
commission)
values (0001, 0.15);

select * from salesman => commission shows as .15

How can I create table where commission would show as 0.15?

problemunsolved
  • 13
  • 2

1 Answers1

1

You have three choices. Either select the column as a string and do the conversion:

select to_char(commission, '0.00')

Or store the value as a string. Leading zeros do not affect the "number". If you care about them, you need to store them explicitly (i.e. as a string) or generate the value as a string in the format you want.

The third option is to reformat at the application level.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks a lot! I just wondered it's kind of a silly thing that under decimal datatype you can't store decimal where is a zero before decimal point.. I'm shocked! Nevermind, saved my afternoon, thanks! – problemunsolved Nov 18 '18 at 17:48
  • 5
    @problemunsolved - How a value is stored very rarely has anything to do with how a value is represented when converted to a string to display on a screen. Don't worry about it until the moment you come to display it, then explicitly convert it to the display format that you want. *(You wouldn't force a monetary value to have a `$` stored with it, don't force a decimal to have the number of 0's that you want.)* – MatBailie Nov 18 '18 at 18:05
  • @MatBailie Yes, I slowly start to understand, that in SQL, not the stored values matter but the represented values. Just seems a bit shocking to me that there isn't a way in SQL that would store very simple decimal values with zeros before the decimal point. They could easily create new data type. Thanks for your feedback! :) – problemunsolved Nov 18 '18 at 18:45
  • 1
    @problemunsolved all of the necessary data is stored, using an internal binary format that is not our concern as end users. How many zeroes to display before or after the decimal point, what character to use to represent the point etc are display format choices and up to the client interface. – William Robertson Nov 18 '18 at 19:00
  • 1
    @problemunsolved It's not specific to SQL. Do you use Any programming languages that store numeric values in human format? They're stored in Binary, fixed point or floating point. – MatBailie Nov 18 '18 at 19:21