How to use TO_NUMBER() and TO_CHAR() to get Number/String in a given format in PL/SQL?

 TO_NUMBER: The structure of TO_NUMBER function is shown below.

TO_NUMBER(string,[format],[NLS params])

The parameters shown in [] are optional. We can simply use TO_NUMBER to convert a string to number as shown below.

TO_NUMBER('32392.849');

This output of the above function call will be 32392.849. But, what about the case when your string contains separators such as , .

Suppose a string 323,567,897.90. Now you want to convert this string into a number. Straightaway using the above function will give an error. This string can be converted into a number as shown below.

TO_NUMBER('323,567,897.90' , '999G999G999D99');

Here G is the second character in NLS_NUMERIC_CHARACTERS set of a session and D is the first character.

Run the following query to see the NLS_NUMERIC_CHARACTERS set of your session

Select * from NLS_SESSION_PARAMETERS.

You can modify the NLS_NUMERIC_CHARACTERS set in your session or pass the custom NLS_NUMERIC_CHARACTERS as the third parameter of TO_NUMBER() function. Now, the output of the above query will be big, but we will see the line of output which is useful to us.

NLS_NUMERIC_CHARACTERS .,

So, if G is the second character then it denotes , , and if D is the first character then it denotes . .

You can pass NLS_NUMERIC_CHARACTERS in the TO_NUMBER() function as follows.

TO_NUMBER('323.567.897,90' , '999G999G999D99' , 'NLS_NUMERIC_CHARACTERS='',.''');

Now, D will denote , ,and G will denote . .

Currency specifier can also be passed into the format as shown below.

TO_NUMBER('$323,567,897.90' , 'L999G999G999D99');

The default currency in NLS_SESSION_PARAMETERS is $. Again, you can pass your own currency as the third parameter of TO_NUMBER function.

NOTE: We cannot have more digits in first parameter than specified in the second parameter of the TO_NUMBER function, both to the left as well as right hand side of decimal.

Now, let's see how to use TO_CHAR function to convert a number into string. The format is same as TO_NUMBER function.

Suppose a number 323567897.90. Now we want to convert this number to String in a given format

TO_CHAR(323567897.90 , 'L999G999G999D99')

and the output will be $323,567,897.90.

Also, there is a minor difference between TO_CHAR and TO_NUMBER. TO_NUMBER doesn't allow you to have more than specified number of digits in the second parameter either on left or right side of decimal in the first parameter. But in TO_CHAR, if you have more number of digits to the right side of decimal than specified in the format, it will round it off, for ex.

TO_CHAR(323567897.83 , 'L999G999G999D9') is perfectly normal in TO_CHAR.

You can always have more digits in format specifier(arg 2) than the actual number(arg1) in either of the function.

THE V FORMAT ELEMENT

Below is the example for V format element.

TO_CHAR(323567897.83 , 'L999G999G999V999')

The output will be $323,567,897830. Did you get the pattern? Overlay arg2 on arg1 with V taking the place of decimal and append extra 0s, if required, on the right side of decimal.

You can always go for various combinations of format specifier and get interesting results.

For more info visit