Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-20-2016
10:01 AM

10-20-2016
10:01 AM

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

Solved! Go to Solution.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-26-2016
12:32 AM

10-26-2016
12:32 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-20-2016
10:41 AM

10-20-2016
10:41 AM

SyntaxEditor Code Snippet

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-20-2016
12:40 PM

10-20-2016
12:40 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-20-2016
05:14 PM

10-20-2016
05:14 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-21-2016
12:11 AM

10-21-2016
12:11 AM

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...

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-26-2016
12:15 AM

10-26-2016
12:15 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-26-2016
12:19 AM

10-26-2016
12:19 AM

Found a solution (without regex).

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-26-2016
12:32 AM

10-26-2016
12:32 AM

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 :-)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-28-2016
02:27 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-28-2016
03:47 PM

10-28-2016
03:47 PM

Hi Dieter,

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

Thanks,

Kamal