CODEWITHSUNDEEP
sqloracle-databaseplsqlrelational-database
Avi
Avi

Reputation: 3

How do I set precision & scale when using CAST in Oracle?

Currently I have CAST($Variable AS varchar2(30)).

I would like to add minimum and maximum precision. For example, the integers to the left of the decimal point in 1234567.89 should have a minimum of 1 but a maximum of five (so the extra integers would be trimmed). In addition, I'd like to do the same for scale - the decimals to the right, by setting a minimum of two decimal places and a maximum of four. These numbers are just examples, the numbers will be updated dynamically.

From what I've read I would have to set precision and scale somehow within type_name in CAST, but I'm not certain how to do that with multiple minimum and maximum of each. I'm an Oracle noob, so I'd appreciate any help!

Upvotes: 0

Views: 4349

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can have cross platform compatibility, but you need to choose the exactly precision and scale. You can do this by converting to a decimal:

select cast(val as decimal(10, 2))

If you want a string, then cast the decimal to a string:

select cast(cast(val as decimal(10, 2)) as varchar(255))

The issue in your question is vague notion of "1 to 5" decimal places. You have to choose the number you actually want.

Upvotes: 0

ramana_k
ramana_k

Reputation: 1933

There seem to be 3 issues here: 1) configure number of digits on the right side of decimal point, 2) configure number of digits on the left side 3) Do this in a way that is portable across multiple databases

First, to do this in Oracle, I agree with Ditto that to_char is the function to use. We need to specify a number format, that in general looks like this: 'FM0999.0099'

Issue 1 can be solved by adjusting the '.0099' part on the right. The two zeroes make sure there are at least two digits on the right of decimal point (zero padded if necessary) and the following two 9's lead to a maximum of 4 digits on the right side.

Regarding issue 2, configuring the minimum number of digits comes with a caveat. In general, we want this minimum to be sufficient to express the given number. The maximum can be adjusted by just zero padding on the left. For this, we need to adjust the '0999' part on the left side of the format by specifying enough 9's and adding one are more 0's on the left to specify maximum number of digits on the left.

Ensuring portability across databases is a challenge. In other databases, we may have to use an entirely different function, like CONVERT.

Upvotes: 0

Ditto
Ditto

Reputation: 3344

I don't believe CAST can do what you want, however, TO_CHAR with proper format can ..

Not sure about cross-platform compatibility, though .. sorry:

  SQL> select to_char('123.45','FM99990.0099') from dual;

  TO_CHAR('12
  -----------
  123.45

  SQL> select to_char('123123.45','FM99990.0099') from dual;

  TO_CHAR('12
  -----------
  ###########

  SQL> select to_char('123.41235','FM99990.0099') from dual;

  TO_CHAR('12
  -----------
  123.4124

Note that when you only "limit" the digits to the left of decimal to 5, giving a larger number produces an "error" style output of all hash marks "#####".

[edit] You can adjust the format model by building it based on incoming inputs:

  SQL> select 'FM' || lpad('0',5,'9') || '.' || rpad('00',4,'9') from dual;

  'FM'||LPAD('
  ------------
  FM99990.0099

  SQL> select 'FM' || lpad('0',7,'9') || '.' || rpad('00',2,'9') from dual;

  'FM'||LPAD('
  ------------
  FM9999990.00

  SQL>

[/edit]

[edit2] for example: you can put it all together like this:

  SQL> !cat q.sql
  Accept len prompt "Length:"
  accept prec prompt "Precision:"

  select to_char('123.45','FM' || lpad('0',&len,'9') || '.' || rpad('00',&prec,'9'))
   from dual;

[/edit]

Upvotes: 1

Related Questions