2

I don't understand fully how can I use the to_char function to convert a number to a string with the appropriate format model.

The actual number has this type of format:

  • Uses comma as decimal separator
  • Always 5 decimal numbers
  • The integer numbers can up to 6 (potentially can be infinite, but for now they were never more than 6)
  • The number can be positive or negative
  • The number can begin with a 0

I've tried to use the to_char but I am not able to achieve a result that works with all the conditions

Some examples of how the output should be are 0,00235 or 156,45623 or -0,0235 or -156,45623

Ponzaro
  • 139
  • 2
  • 5
  • 16
  • 1
    What have you tried? What is the data type of the number (precision, scale). Numbers do not begin with 0. The string representation of a number may have leading zeros. – OldProgrammer Jul 16 '19 at 19:28
  • Possible duplicate of [Oracle - Why does the leading zero of a number disappear when converting it TO\_CHAR](https://stackoverflow.com/questions/6695604/oracle-why-does-the-leading-zero-of-a-number-disappear-when-converting-it-to-c) – Jacob H Jul 16 '19 at 19:29
  • `TO_CHAR` format models are documented [here](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Format-Models.html#GUID-096CA64F-1DA3-4C49-A18B-ECC7518EE56C). – William Robertson Jul 16 '19 at 20:12

2 Answers2

1

Shamelessly stolen from this post from @Vadzim.

You should be able to get the format you're looking for by using this pattern:

rtrim(to_char(num, 'FM999999999999990.99'), '.')

https://rextester.com/QRSD48676

SELECT rtrim(to_char('0,00235', 'FM999999999999990.99999'), '.') FROM DUAL\\
SELECT rtrim(to_char('156,45623', 'FM999999999999990.99999'), '.') FROM DUAL\\  
SELECT rtrim(to_char('-0,0235', 'FM999999999999990.99999'), '.') FROM DUAL\\
SELECT rtrim(to_char('-156,45623', 'FM999999999999990.99999'), '.') FROM DUAL\\

Results:

0.00235
156.45623
-0.0235
-156.45623
Jacob H
  • 2,455
  • 1
  • 12
  • 29
1

Keep in mind that you are transforming a number into a string. The number does not have any sense of "," or "." or anything--it is a number.

The trick is to get the TO_CHAR function to convert the internal number to the string representation that you want. There are a few problems to worry about: getting the radix point (decimal) correct and dealing with padding.

Here is a working example:

SELECT to_char(0.00235,'FM99999999999999990D99999', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL;

0,00235

SELECT to_char(156.45823,'FM99999999999999990D99999', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL;

156,45823

SELECT to_char(-0.0235,'FM99999999999999990D99999', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL;

-0,0235

SELECT to_char(-156.45623,'FM99999999999999990D99999', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL;

-156,45623

SELECT to_char(123456789.45623,'FM99999999999999990D99999', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL;

123456789,45623

The relevant parts of the mask:

FMis used to trim leading and trailing blanks that Oracle normally uses to pad out numbers.

D is the radix point, depending on your NLS settings.

NLS_NUMERIC_CHARACTERS ... is an override of your local NLS settings--this might not be necessary if your locale uses a comma for the decimal, but it is a way you can force this behavior in a database with, say, North American settings.

Tad Harrison
  • 1,258
  • 5
  • 9