Question: I have a table with column node\_id as number, where,

```
node_id
0
2000
300300300
400400400

```

what i am trying to get is convert this number into string and add '-' after every third digit from right.
So expected output is,

```
node_id
000-000-000
000-002-000
300-300-300
400-400-400

```

This is the query which i am using,

```
select TO_CHAR( lpad(t1.node_id,9,'0'), '999G999G999', 'NLS_NUMERIC_CHARACTERS="-"'), node_id from table t1;

```

The output i am getting is,

```
node_id
0
2-000
300-300-300
400-400-400

```

My problem is I also need to prepend '0' to each record such that the total length is 11.
I tried adding to\_char immediately around lpad so as to convert the lpad output to varchar, but that also gives the same output.

Answer: 
Just change your format mask to:

```
'099G999G999'

```

(Note the leading '0')
An alternative for prepending 0s that works in any database is something like:

```
right('000000000000' || cast(<val> to varchar(100)), <numdigits>)

```

Of course, some databases use "concat()" or "+" instead of "||" for concatenation.