How to convert number to varchar with leading 0's

Database
Enthusiast

How to convert number to varchar with leading 0's

Hi,

 

I have a number column and we want to convert it to varchar. The output of cast and to_char functions removes the leading 0's. Can someone please suggest a way to keep the leading 0's?

 

Number value -0.24448 gives output as -.24448

Number value  0.457294762 gives output as .457294762

 

Thanks,

Kamal


Accepted Solutions
Senior Apprentice

Re: How to convert number to varchar with leading 0's

Of course using a FORMAT is best for a fixed known number of digits (@bhull63's answer).

 

But your original questions was for a Number =  an unknown number of fractional digits :-)

1 ACCEPTED SOLUTION
10 REPLIES
Enthusiast

Re: How to convert number to varchar with leading 0's

SyntaxEditor Code Snippet

sel -.24448 (format '----9.99999999') (varchar(14));
Enthusiast

Re: How to convert number to varchar with leading 0's

Thanks for the response. This works for values with decimal points but for values like 251555854, it displays the value as ************************. I added a few more dashes in the format and it displayed the value correctly. Can you please explain the significance of a dash in format? Also is there a limit on number of dashes?

 

Thanks,

Kamal

Enthusiast

Re: How to convert number to varchar with leading 0's

The dashes are a floating negative sign. If the number is negative, it will float until it hits the first non-zero value. If the number is positive, it floats to the first non-zero value but does not display the negative sign. You can also put commas as separators for thousands millions, etc. For example, you could specify:
Format '--,---,--9.999999999'
Adjust your varchar legth to the length of your format clause. I believe that you can specify up to 38 significant digits in the format clause.
Senior Apprentice

Re: How to convert number to varchar with leading 0's

If you really need a flexible number of fractional digits you can use a RegEx to add a zero before a digit which is not preceded by a zero:

RegExp_Replace(To_Char(n), '(?<!\d)(\.)', '0\1')

 

I don't know why there's no format for NUMBER which includes a leading zero, would be much easier...

Enthusiast

Re: How to convert number to varchar with leading 0's

Thanks Dieter! Works fine!

Would there be a regex to add a number of 0's up to a certain length? (e.g. such that the length would be 5)

123 => 00123

1 => 00001

65494 => 65494

 

Thanks, KR,

Carl

Enthusiast

Re: How to convert number to varchar with leading 0's

Found a solution (without regex).

select TRIM((123 (INTEGER)) (FORMAT '99999'));

Senior Apprentice

Re: How to convert number to varchar with leading 0's

Of course using a FORMAT is best for a fixed known number of digits (@bhull63's answer).

 

But your original questions was for a Number =  an unknown number of fractional digits :-)

Enthusiast

Re: How to convert number to varchar with leading 0's

Thanks @dnoeth & @bhull63. Really appreciate your responses. We have varying number of digits so the solution using regexp_replace works best for us.

 

Thanks!

Enthusiast

Re: How to convert number to varchar with leading 0's

Hi Dieter,

 

Can you please explain the schematics of the regular expression used?

 

Thanks,

Kamal