CODEWITHSUNDEEP
sqloracle-database
Kshitij
Kshitij

Reputation: 8614

How to prepend zero to a number along with formatting it

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.

Upvotes: 1

Views: 406

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

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.

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132570

Just change your format mask to:

'099G999G999'

(Note the leading '0')

Upvotes: 3

Related Questions