Database

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.