Hong Van Vit
Hong Van Vit

Reputation: 2986

how can to_char with zero before number in oracle

I have database like below:

WITH TB AS(
     SELECT 1 NONB FROM DUAL UNION ALL
     SELECT 89 NONB FROM DUAL UNION ALL
     SELECT 193 NONB FROM DUAL  
)
SELECT * FROM TB

I want change column NONB to_char(NONB) and display zero before the number like below.

001

089

189

How can I do this? Thanks.

Upvotes: 0

Views: 62

Answers (2)

Fact
Fact

Reputation: 2460

Use this:

WITH TB AS(
     SELECT 1 NONB FROM DUAL UNION ALL
     SELECT 89 NONB FROM DUAL UNION ALL
     SELECT 193 NONB FROM DUAL  
)
 SELECT to_char(nonb, 'FM000') FROM TB

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Use lpad():

select lpad(nonb, 3, '0')
from tb;

Here is a rextester.

Upvotes: 2

Related Questions