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

Here is one option with varchar2 datatype:

with test as (
                select '123456789012' a from dual
              )
select listagg(substr(a,(level-1)*3+1,3),'/') within group (order by rownum) num
from test
connect by level <=length(a)

or

with test as (
                select '123456789012.23' a from dual
              )
select '$'||listagg(substr((regexp_substr(a,'[0-9]{1,}')),(level-1)*3+1,3),',') within group (order by rownum)||regexp_substr(a,'[.][0-9]{1,}') num
from test
connect by level <=length(a)

output:

1st query

123/456/789/012

2nd query

$123,456,789,012.23

Answer 2

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.