CODEWITHSUNDEEP
sqloracle11goracle-sqldeveloper
Menkes
Menkes

Reputation: 391

Transforming comma to number and limiting to 3 decimal places

I have values structured like this:

0,132
6,0999999999999999E-2

And I want it to become this:

0.132
0.060

Using an sql query in oracle db?

Turning the comma to dot and maintaining only 3 decimal points. Tried CAST and TO_NUMBER with formatting but it didn't work.

Upvotes: 1

Views: 402

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

You can also use the optional third argument to the to_number() function to specify the comma as the decimal separator. With a CTE to provide your string values:

with t (str) as (
  select '0,132' from dual
  union all select '6,0999999999999999E-2' from dual
)
select to_number(str, '9D9999999999999999EEEE', 'NLS_NUMERIC_CHARACTERS='',.''')
  as num
from t;

      NUM
----------
      .132
      .061

The format model needs to have enough digits after the decimal separator for the most precise number in your table. The EEEE format handles the scientific notation.

You can then use round() or trunc() to restrict to three decimal places; rounding makes no difference with your sample values, but truncating shows that the second value goes from 0.061 to 0.060:

with t (str) as (
  select '0,1322' from dual
  union all select '6,0999999999999999E-2' from dual
)
select trunc(to_number(str, '9D9999999999999999EEEE',
  'NLS_NUMERIC_CHARACTERS='',.'''), 3)  as num
from t;

       NUM
----------
      .132
       .06

You can use to_char() (or your client/application) to show leading and trailing zeros if you prefer.

You could also set the session NLS_NUMERIC_CHARACTERS to ',.', but it's safer to not assume anyone running this query will have a particular setting and make it part of the query.

Upvotes: 0

sagi
sagi

Reputation: 40481

You can use ORACLE's to_number,replace and round functions like this:

SELECT round(to_number(replace(string,',','.')),3) FROM dual

Upvotes: 2

Related Questions