TO_Char number format model in Oracle
Solution 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:
FM
is 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.
Solution 2
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
Related videos on Youtube
Ponzaro
Updated on June 04, 2022Comments
-
Ponzaro almost 2 years
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
or156,45623
or-0,0235
or-156,45623
-
OldProgrammer almost 5 yearsWhat 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.
-
Jacob H almost 5 yearsPossible duplicate of Oracle - Why does the leading zero of a number disappear when converting it TO_CHAR
-
William Robertson almost 5 years
TO_CHAR
format models are documented here.