ORACLE TO_CHAR SPECIFY OUTPUT DATA TYPE

Question

I have column with data such as '123456789012' I want to divide each of each 3 chars from the data with a '/' in between so that the output will be like: "123/456/789/012"

I tried "SELECT TO_CHAR(DATA, '999/999/999/999') FROM TABLE 1" but it does not print out the output as what I wanted. Previously I did "SELECT TO_CHAR(DATA, '$999,999,999,999.99') FROM TABLE 1 and it printed out as "$123,456,789,012.00" so I thought I could do the same for other case as well, but I guess that's not the case.

There is also a case where I also want to put '#' in front of the data so the output will be something like this: #12345678901234. Can I use TO_CHAR for this problem too?

Is these possible? Because when I go through the documentation of oracle about TO_CHAR, it stated a few format that can be use for TO_CHAR function and the format that I want is not listed there.

Thank you in advance. :D

Answer 1

If you wants groups of three then you can use the group separator G, and specify the character to use:

SELECT TO_CHAR(DATA, 'FM999G999G999G999', 'NLS_NUMERIC_CHARACTERS=./') FROM TABLE_1

123/456/789/012

If you want a leading # then you can use the currency indicator L, and again specify the character to use:

SELECT TO_CHAR(DATA, 'FML999999999999', 'NLS_CURRENCY=#') FROM TABLE_1

#123456789012

Or combine both:

SELECT TO_CHAR(DATA, 'FML999G999G999G999', 'NLS_CURRENCY=# NLS_NUMERIC_CHARACTERS=./') FROM TABLE_1

#123/456/789/012

db<>fiddle

The data type is always a string; only the format changes.