CODEWITHSUNDEEP
sqloracle-database
SDR
SDR

Reputation: 391

How to retain datatype and decimals after cast - Oracle

This must be a silly question but I'm unable to find a proper way to handle the below scenario.
My objective is to take a number in varchar column and cast it to decimal. However, I also need to make sure that the decimal places remain intact. How do I achieve this and make sure the datatype will remain to DECIMAL(13,3).

 select TO_CHAR(CAST(11.0001 AS DECIMAL(13,3)) ,9999999999.999) from dual;  -- O/p : 11.000; Datatype : VARCHAR2

 select TO_CHAR(CAST(0.0001 AS DECIMAL(13,3)) ,9999999999.999) from dual; -- O/p : .000; Datatype : VARCHAR2

 select CAST(11.0001 AS DECIMAL(13,3)) from dual; -- O/p : 11; Datatype : DECIMAL(13,3)

 select CAST(0.0001 AS DECIMAL(13,3)) from dual; -- O/p : 0; Datatype : DECIMAL(13,3)

What I am looking for as an output would be :

Input : 11.0001  VARCHAR(40)  Output : 11.000 DECIMAL(13,3)
Input : 0.0001 VARCHAR(10)    Output : 0.000 DECIMAL(13,3)
Input : 18     VARCHAR(40)    Output : 18    DECIMAL(13,3)

Any help would be appreciated.

Thanks

Upvotes: 0

Views: 186

Answers (1)

eaolson
eaolson

Reputation: 15094

I don't think you're asking the right question. Numbers do not have decimal places. 18 is the same as 18.0 is the same as 18.00. You can format a number, which gives you a string formatted in the desired way, with however many digits you want.

If you are taking these VARCHAR2's and converting them to numerics, but want to know how they were previously displayed, then you are losing information. Why do you need to convert these to decimals in the first place? Are you just trying to take things with 4 digits of precision and remove anything past the 3rd digit?

Upvotes: 1

Related Questions