CODEWITHSUNDEEP
sqloracle-database
Ashish Kumar
Ashish Kumar

Reputation: 11

How can I format the decimal value without decimal?

eg 18.45 should be 00000000001845000 datatype suppose number(x,5) so last five digits are for precision

Upvotes: 0

Views: 152

Answers (3)

Alex Poole
Alex Poole

Reputation: 191235

Another option is to use the V format model element; from the documentation:

Element Example Description
V 999V99 Returns a value multiplied by 10n (and if necessary, round it up), where n is the number of 9's after the V.

So you can do:

select to_char(18.45, '000000000000V00000') from dual;

TO_CHAR(18.45,'000000000000V00000')
-----------------------------------
 00000000001845000

or without the leading space (which is a placehold for a minus sign in case there are negative values):

select to_char(18.45, 'FM000000000000V00000') from dual;

TO_CHAR(18.45,'FM000000000000V00000')
-------------------------------------
00000000001845000

db<>fiddle

Upvotes: 2

Vahram Danielyan
Vahram Danielyan

Reputation: 309

Also you can multiply for 100000 the given number:

SELECT TO_CHAR(18.45 * 100000, '00000000000000000') FROM DUAL;

Upvotes: 1

D. Mika
D. Mika

Reputation: 2798

This should do it:

SELECT REPLACE(TO_CHAR(18.45, 'FM000000000000D00000', 'NLS_NUMERIC_CHARACTERS=''.,'''), '.', '') FROM DUAL;

The NLS_NUMERIC_CHARACTERSmakes sure the decimal separator is a . regardless what the session is configured. This way we're safe to remove it from the resulting string with the replace function.

The FM is used to suppress the leading space character.

Upvotes: 0

Related Questions