Re: SQL: Left Justified With Zero But Maintaining 4 digits of Number.

From: DanHW <danhw_at_aol.com>
Date: 18 Sep 1998 03:16:33 GMT
Message-ID: <19980917231633.03944.00000917_at_ng99.aol.com>


>I have problem again.
>I want to create a number with 4 digits.Using SQL or PL/SQL.
>The number will be in a sequential pattern (as an ID).
>
>The problem is, how can I format the number so that it can be always
>" left justified with 0 (Zero) but maintaining the 4 digit limits ".
>
>Here are the one I need:
>---------------------------
>if the number is less than 0 - it will be 0001,0002,0003 .... (4 digits)
>if the number is more than 100
>but less than 1000 - it will be 0100,0101....0999.. (4 digits)
>if the number is more than 1000 - it will be 1000,...5000,.... (4 digits)
>
>and so on...
>I like to use a LOOP control structure but I cannot make a header
>of 0 because it will go over the 4 digits limit, say if 0 is a header
>
>for 1 it will be 01 (2 digits)
>for 10 it will be 010 (3 digits)
>for 100 it will be 0100 (4 digits)
>for 1000 it will be 01000 (5 digits)
>
>The reason why I "must maintain the 4 digits structure" is because this
>number
>will later on used as a bar code reference which have a format of
>4 digits. Does anybody out there have an idea?Can it be done?
>
>Appreciate your help.Thanks.
>Norazman
>

To save the number as you described, you need to store it as varchar2. When you write out a number, the normal format specifier is a '9', which prints the number or a null. If you use a '0' instead, you will get a 0 or the number. Thus you can do this

displayed_num := to_char(read_num,'0000');

This will actually return a 5 digit string, with the leading character being a space (Oracle is allowing space for a minus sign). Assuming you don't need that you can either do a substr on it, or a replace...

displayed_num := substr( to_char(read_num,'0000'), 2);

or

displayed_num := replace ( to_char(read_num,'0000'),' ');

Hope this helps...

Dan Hekimian-Williams Received on Fri Sep 18 1998 - 05:16:33 CEST

Original text of this message